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

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 = ' ||

Answer 2

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

Answer 3

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

Answer 4

Concate with above :

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

Answer 5

What do you mean by dynamic table?