Thread: 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@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
> 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¤cy_code=USD