Create dynamic table from function in PostgreSQL

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$BEGINRETURN ($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.

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;

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).