Re: how to create a multi columns return function ?

Yo,

I have run into this problem in the past as well.. Even though there is
probably a more elegant solutions, I found that creating a Composite
Type with all the required fields and returning that works petty decent.

E.g.
CREATE TYPE "public"."rs_dummytype" AS (
"column1" VARCHAR,
"column2" VARCHAR,
"column3" VARCHAR,
"column4" VARCHAR,
"column5" VARCHAR,
"column6" VARCHAR,
"column7" VARCHAR,
"column8" VARCHAR,
"column9" VARCHAR,
"column10" VARCHAR
);

create function function_name( int ) returns rs_dummytype AS '
DECLARE
input1 ALIAS FOR $1;
recordset rs_dummytype%ROWTYPE;
BEGIN
FOR recordset IN SELECT column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10 FROM table_name WHERE
column1 = input1 AND column5 = ''specific value'' AND column8 =
''specific_value2''
LOOP
RETURN NEXT recordset;
END LOOP;

RETURN;
END;
' LANGUAGE 'plpgsql'

Jordan S. Jones

jclaudio(at)capitol(dot)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
> 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

--
I am nothing but a poor boy. Please Donate..
https://www.paypal.com/xclick/business=list%40racistnames.com&item_name=Jordan+S.+Jones&no_note=1&tax=0&currency_code=USD