Skip site navigation (1) Skip section navigation (2)

function to return rows as columns?

From: Linos <info(at)linos(dot)es>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: function to return rows as columns?
Date: 2009-02-27 10:41:56
Message-ID: 49A7C374.7070503@linos.es (view raw or flat)
Thread:
Lists: pgsql-general
Hello,
	i have a query that returns a result set like this:

item | size | stock
123  | XL   | 10
123  | XXL | 5
123  | XS   | 3

and i would like get the results like this:

item | XL | XXL | XS
123  | 10  | 5     | 3

i have been thinking how to do it with a plpgsql function but the number of 
sizes depend on the item that it is queried so i can not create a type and 
return it, i could create it like a text concatenating the stock and size of 
every row and returning the complete line text but i would have to process it in 
the application anyway so i am searching a solution that lets me return it like 
a record.

I have been searching the list and maybe i could create the record type inside 
the function and to get the correct names and number of columns in the 
application side launching a query to get the number of sizes before call the 
function to specify the columns in the function call but maybe i am missing 
anything important here? any better (or more correct) way to do this? Thanks.

Regards,
Miguel Angel.

Responses

pgsql-general by date

Next:From: Thomas KellererDate: 2009-02-27 10:56:26
Subject: Re: function to return rows as columns?
Previous:From: Scott MarloweDate: 2009-02-27 10:18:26
Subject: Re: funny view/temp table problem with query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group