Hello,
as you don't seems to need the returned column definition in Postgres, a solution may be to cast the result to text. e.g.: create or replace function get_record () returns setof text as $$ select (foo)::text from (values(1,'a a'),(3,'b b'))foo $$ language sql; select trim(r,'\\(\\)') from get_record () r; regards, Marc Mamin From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Misa Simic Sent: Montag, 6. Februar 2012 19:52 To: Andrus Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to create crosstab with 3 values in every crosstab column Hi, Well, I think you will need to write your own function(s) which will solve your particular case... There are two ways explaind in last mails... Dynamic SQL or direct export to file... Kind Regards, Misa 2012/2/6 Andrus <kobrule...@hot.ee> Thank you. the point is - it is not possible to get unknown no of columns in 1 SQL query... i.e. Account, Store, Amount 100, St1, 1000.00 100, St2, 2000.00 to get: Acount, St1 , St2 100, 1000.00 2000.00 to get that in your query... St1 and St2 - must be hardcoded... (is there 1 column per Store, or 3 columns per store it >is less important...) if it St1 and St2 are hardcoded in query, even if in table is: Account, Store, Amount 100, St1, 1000.00 100, St2, 2000.00 100, St3, 3000.00 We would get the same result...actually if we want St3 we need to change our query and add St3 in it... that is the reason why we use Dynamic SQL to build the query... when you build your Dynamic SQL query... you could use COPY (dynamicQuery) TO CSV file... or instead of to build dynamic query, you can export directly to file... I din't knwo this. This seems very serious limitation which makes crosstab useless . I tried create temp table sales ( account char(10), store char(10), sales numeric(12,2) ) on commit drop; insert into sales values ('311','ST1',100), ('311','STN',200), ('312','ST1',400), ('312','STN',600); select * from crosstab('select * from sales', 'select distinct store from sales' ) x and got error ERROR: a column definition list is required for functions returning "record" Can we use something like select * from dynamicwrapper( crosstab('select * from sales', 'select distinct store from sales' )) x Where to find generic dynamicwrapper stored procedure which fixes this by building dynamic query itself or other idea ? Andrus.
