Alex Rivera | Logout

PostgreSQL function or stored procedure that outputs multiple columns?

Asked 2011-03-28T17:49:30.633
16

Here is what I ideally want. Imagine that I have a table with the row A.

I want to do:

SELECT A, func(A) FROM table

and for the output to have say 4 columns.

Is there any way to do this? I have seen things on custom types or whatever that let you sort of get a result that would look like

A,(B,C,D)

But it would be really great if I could have that one function return multiple columns without any more finagling.

Is there anything that can do something like this?

postgresql stored-procedures plpgsql sql-function
Edit
Report

1 Answer

0

I think you will want to return a single record, with multiple columns? In that case you can use the return-type RECORD for example. This will allow you to return an anonymous variable with as many columns as you want. You can find more information about all the different variables here:

http://www.postgresql.org/docs/9.0/static/plpgsql-declarations.html

And about return types:

http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS

If you want to return multiple records with multiple columns, first check and see if you have to use a stored procedure for this. It might be an option to just use a VIEW (and query it with a WHERE-clause) instead. If that's not a good option, there is the possibility of returning a TABLE from a stored procedure in version 9.0.

answered 2011-03-28T22:11:13.493

Your Answer