+ 14
Is anyone know how to create dynamic table in PostgreSql database?
5 Answers
+ 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?