Thread: how to create a multi columns return function ?

<br /><font face="sans-serif" size="2">Hi</font><br /><br /><font face="sans-serif" size="2">I'm moving databases from
sybaseto postgres.</font><br /><font face="sans-serif" size="2">But I have difficulties in creating a postgres
equivalentto the sybase stored procedures...</font><br /><br /><font face="sans-serif" size="2">Apparently, Postgres
functionsshould work, but the syb stored procedures get only one parameter and return several colums</font><br /><br
/><fontface="sans-serif" size="2">Here's the code I wrote in postgresql :</font><br /><br /><font face="sans-serif"
size="2">createfunction function_name( int ) returns text</font><br /><font face="sans-serif" size="2">AS ' SELECT
column1,column2, column3,...,column15</font><br /><font face="sans-serif" size="2">FROM table_name</font><br /><font
face="sans-serif"size="2">WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 = \'specific_value2 \'
'</font><br/><font face="sans-serif" size="2">LANGUAGE 'SQL';</font><br /><br /><font face="sans-serif" size="2">and I
getthe message error : returns multi columns</font><br /><br /><font face="sans-serif" size="2">I'm wondering too if
It'spossible to create a view with a parameter if functions don't work.</font><br /><br /><font face="sans-serif"
size="2">Hasanybody faced the same problem ?</font><br /><br /><font face="sans-serif" size="2">I need help</font><br
/><br/><font face="sans-serif" size="2">thanks</font> 

Take a look at the section on 'SQL Functions Returning Sets'


http://www.postgresql.org/docs/7.3/static/xfunc-sql.html#AEN31304



> Hi 
> 
> 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
> FROM table_name 
> WHERE colum1 = $1 AND column5 = \'specific value\' AND column8 =
> \'specific_value2 \' '
> LANGUAGE 'SQL'; 
> 
> 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 
> 
> thanks



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

On Tuesday 21 October 2003 14:08, jclaudio@capitol.fr wrote:
> Hi
>
> 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

You've said it's returning "text" whereas it's returning whatever your columns 
are. You'll want to do something like:

CREATE TYPE fn_ret_type AS ( column1 int4, column2 text, column3 date,...
);

CREATE FUNCTION function_name(int) RETURNS fn_ret_type ...

If it returns multiple rows you want SETOF fn_ret_type
--  Richard Huxton Archonet Ltd