October 21st, 2003, 05:21 AM
how to create a multi column return function
I'm moving databases from sybase to postgres.
But I have difficulties in creating a postgres equivalent to the sybase stored procedures...
Apparently, Postgres functions should work, but the syb stored procedures get only one parameter and return several colums
Here's the code I wrote in postgresql :
create function function_name( int ) returns text
AS ' SELECT column1, column2, column3,...,column15
WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 = \'specific_value2 \' '
and I get the message error : returns multi columns
I'm wondering too if It's possible to create a view with a parameter if functions don't work.
Has anybody faced the same problem ?
I need help
November 15th, 2003, 04:32 PM
I think your problem is that you're returning many columns but you've declared a return type of TEXT. I'm not sure exactly what you want to return based on your function, but Postgres allows you to return a row based on a table's structure, in which case you'd want to do:
CREATE FUNCTION function_name(INT) RETURNS table_name%ROWTYPE
or you can create a custom type if the data you're returning isn't specific to a table. In which case you'd have to do:
CREATE TYPE my_data_type AS (
[insert more definitions here]
CREATE FUNCTION function_name(INT) RETURNS my_data_type
If you want to return more than one row, you can just add "SET OF" to your definition (e.g. RETURNS SET OF table_name%ROWTYPE)
Or, I can't think why you'd want to, but you could concatenate everything together into a string to return it all as text. I'd rethink things if that's what you want to do. It would be terrible design.
The Postgres docs aren't great when it comes to stored procedures, but hunt around in http://techdocs.postgresql.org/ for examples. I'd start at http://techdocs.postgresql.org/guide...rningFunctions
I'm still learning about views, so I can't answer your other question. I didn't know any of this a month ago, so it's not so hard to find out Good luck.