Create dynamic table from function in PostgreSQL

Question

I have table data,

select * from tbltaxamount ;
 id |   taxname   | taxinfoid | taxvalue | taxamt | zoneid | invoiceid | transid 
----+-------------+-----------+----------+--------+--------+-----------+---------
  1 | Service Tax |         0 |     0.00 |  28.69 |      2 |       119 |      -1
  2 | ABC Tax     |         0 |     0.00 |  25.78 |      2 |       119 |      -1

Now, how can I get the result as below using any function of PostgreSQL?

invoiceid | Service Tax | ABC Tax
----------+-------------+--------
      119 |       28.69 |  25.78

Answer 1

Your solution is a viable way. I largely rewrote your plpgsql function for simplification / performance / readability / security.

CREATE OR REPLACE FUNCTION f_taxamount()
 RETURNS void AS
$BODY$
DECLARE
    rec record;
BEGIN

    DROP TABLE IF EXISTS tmptable;

    EXECUTE 'CREATE TABLE tmptable (invoiceid integer PRIMARY KEY, '
        || (
           SELECT string_agg(col || ' numeric(9,2) DEFAULT 0', ', ')
           FROM  (
              SELECT quote_ident(lower(replace(taxname,' ','_'))) AS col
              FROM   tbltaxamount
              GROUP  BY 1
              ORDER  BY 1
              ) x
           )
        || ')';

    EXECUTE '
        INSERT INTO tmptable (invoiceid)
        SELECT DISTINCT invoiceid FROM tbltaxamount';

    FOR rec IN
        SELECT taxname, taxamt, invoiceid FROM tbltaxamount ORDER BY invoiceid
    LOOP
        EXECUTE '
            UPDATE tmptable
            SET ' || quote_ident(lower(replace(rec.taxname,' ','_')))
                  || ' = '|| rec.taxamt || ' 
            WHERE invoiceid = ' || rec.invoiceid;
    END LOOP;

END;
$BODY$ LANGUAGE plpgsql;

This works for PostgreSQL 9.1 or later.

For pg 8.4 or later replace

SELECT string_agg(col || ' numeric(9,2) DEFAULT 0', ', ')

with:

SELECT array_to_string(array_agg(col || ' numeric(9,2) DEFAULT 0'), ', ')

For versions even older than that create an aggregate function like this:

CREATE OR REPLACE FUNCTION f_concat_comma(text, text)
  RETURNS text AS
$BODY$
BEGIN
RETURN ($1 || ', '::text) || $2;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;

CREATE AGGREGATE concat_comma(text) (
  SFUNC=f_concat_comma,
  STYPE=text
);

And then write:

SELECT concat_comma(col || ' numeric(9,2) DEFAULT 0')

---

Also:

DROP TABLE IF EXISTS tmptable;

The clause "IF EXISTS" was introduced with version 8.2.
If you should use a version even older than that you should you can:

IF EXISTS (
    SELECT *
    FROM   pg_catalog.pg_class
    WHERE  oid = 'tmptable'::regclass
    AND    relkind = 'r')
THEN
    DROP TABLE tmptable;
END IF;
*/

---

Upgrade!

Have a look at the versioning policy of the PostgreSQL project. Version 8.0.1 is an especially buggy version. I would strongly advise you to upgrade. If you can't upgrade to a newer major version, at least upgrade to the latest point-release for security reasons, 8.0.26 in your case. This can be done in place, without changing anything else.

Answer 2

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
    LOOP
        EXECUTE 'UPDATE tmptable SET ' || replace(invoiceids.taxname,' ','') || ' = ' || invoiceids.taxamt  || ' WHERE invoiceid = ' || invoiceids.invoiceid;
    END LOOP ;
RETURN;
END;
$$ LANGUAGE plpgsql;

Answer 3

Could look like this:

SELECT invoiceid
      ,sum(CASE WHEN taxname = 'Service Tax' THEN taxamt ELSE 0 END) AS "Service Tax"
      ,sum(CASE WHEN taxname = 'ABC Tax'     THEN taxamt ELSE 0 END) AS "ABC Tax"
FROM   tbltaxamount 
GROUP  BY 1

---

Depending on what you actually want to achieve, you might be interested in the tablefunc module that can be used to create pivot tables. Here is an example.

If you insist on column names derived from data, you will have build your query dynamically, with a plpgsql function like you did or an anonymous code block (DO statement).