+ 14

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

6th Aug 2018, 9:07 PM
A͢J
A͢J - avatar
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 = ' ||
24th Feb 2019, 4:47 AM
Ashish Maurya
Ashish Maurya - avatar
+ 2
Dynamic table means table_1, table_2, table_3 and so on..
10th Aug 2018, 8:19 PM
A͢J
A͢J - avatar
+ 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
24th Feb 2019, 4:49 AM
Ashish Maurya
Ashish Maurya - avatar
+ 1
Concate with above : LOOP EXECUTE 'UPDATE tmptable SET ' || replace(invoiceids.taxname,' ','') || ' = ' || invoiceids.taxamt || ' WHERE invoiceid = ' || invoiceids.invoiceid; END LOOP ; RETURN; END; $ LANGUAGE plpgsql;
24th Feb 2019, 4:50 AM
Ashish Maurya
Ashish Maurya - avatar
0
What do you mean by dynamic table?
6th Aug 2018, 9:28 PM
JME