Is anyone know how to create dynamic table in PostgreSql database? | Sololearn: Learn to code for FREE!

+14

Is anyone know how to create dynamic table in PostgreSql database?

sql

8/6/2018 9:07:50 PM

A͢J

5 Answers

New Answer

+1

I have gone an example may it will help you: CREATE OR REPLACE FUNCTION taxamount() RETURNS void as $$ DECLARE columnNames RECORD; invoiceids RECORD; BEGIN FOR columnNames IN SELECT * from pg_tables where tablename = 'tmptable' LOOP DROP TABLE tmptable ; END LOOP; CREATE TABLE tmptable (invoiceid integer PRIMARY KEY); FOR columnNames IN SELECT distinct(replace(taxname,' ','')) as taxnames from tbltaxamount LOOP EXECUTE 'ALTER TABLE tmptable ADD ' || columnNames.taxnames || ' numeric(9,2) DEFAULT 0'; END LOOP; FOR invoiceids IN SELECT distinct(invoiceid) from tbltaxamount LOOP EXECUTE 'INSERT INTO tmptable (invoiceid) VALUES (' || invoiceids.invoiceid || ')'; END LOOP; FOR invoiceids IN SELECT * from tbltaxamount LOOP EXECUTE 'UPDATE tmptable SET ' || replace(invoiceids.taxname,' ','') || ' = ' || invoiceids.taxamt || ' WHERE invoiceid = ' ||

+2

Dynamic table means table_1, table_2, table_3 and so on..

+1

I have gone an example may it will help you : After so may tries I have created below function for creation of the table on the fly and that will display records as above. CREATE OR REPLACE FUNCTION taxamount() RETURNS void as $$ DECLARE columnNames RECORD; invoiceids RECORD; BEGIN FOR columnNames IN SELECT * from pg_tables where tablename = 'tmptable' LOOP DROP TABLE tmptable ; END LOOP; CREATE TABLE tmptable (invoiceid integer PRIMARY KEY); FOR columnNames IN SELECT distinct(replace(taxname,' ','')) as taxnames from tbltaxamount LOOP EXECUTE 'ALTER TABLE tmptable ADD ' || columnNames.taxnames || ' numeric(9,2) DEFAULT 0'; END LOOP; FOR invoiceids IN SELECT distinct(invoiceid) from tbltaxamount LOOP EXECUTE 'INSERT INTO tmptable (invoiceid) VALUES (' || invoiceids.invoiceid || ')'; END LOOP; FOR invoiceids IN SELECT * from tbltaxamount

+1

Concate with above : LOOP EXECUTE 'UPDATE tmptable SET ' || replace(invoiceids.taxname,' ','') || ' = ' || invoiceids.taxamt || ' WHERE invoiceid = ' || invoiceids.invoiceid; END LOOP ; RETURN; END; $$ LANGUAGE plpgsql;

0

What do you mean by dynamic table?