Hi All!
First of all, a great Thanks, your suggestions works fine.
I'll hope to enhance a little bit my understanding of SETOF return type.
I have now two problems.
1) I would like to return some columns from one table in PL/pgSQL function. What's in this case the correct return type of the PL/pgSQL function. This is a pseudo-code for my first problem:
--------------------------------------------------------------------
CREATE FUNCTION function_name (sometypes) RETURNS return_type AS $$
FOR some_type IN SELECT column1, column3 FROM tablename WHERE some_conditions
LOOP
RETURN NEXT some_type;
END LOOP;
RETURN;
$$ LANGUAGE 'plpgsql' STABLE;
--------------------------------------------------------------------
What's return_type and some_type in this case?
2) The next problem is almost same as above. But now, I would like to return different columns from different tables.
What's in this case the correct return type of PL/pgSQL function.
This is a pseudo-code for my second problem:
--------------------------------------------------------------------
CREATE FUNCTION function_name (sometypes) RETURNS return_type AS $$
FOR some_type IN SELECT column1_table1, column17_table2 FROM table1, table2 WHERE some_conditions
LOOP
RETURN NEXT some_type;
END LOOP;
RETURN;
$$ LANGUAGE 'plpgsql' STABLE;
--------------------------------------------------------------------
Thanks in advance
Younes
----Message d'origine----
>A: Ycrux <ycrux@xxxxxxxxxxxxxxxx>
>Copie à: pgsql-general@xxxxxxxxxxxxxx
>Sujet: Re: [GENERAL] PL/pgSQL question
>Date: Thu, 09 Mar 2006 19:25:52 -0500
>De: Tom Lane <tgl@xxxxxxxxxxxxx>
>
>Ycrux <ycrux@xxxxxxxxxxxxxxxx> writes:
>> # SELECT grantAccess('sara', 'sarapass');
>> ERROR: set-valued function called in context that cannot accept a set
>
>You need to do "SELECT * FROM grantAccess(...)". This is a plpgsql
>implementation restriction that we'll probably try to fix someday,
>although there's also a school of thought that says that set-returning
>functions in the SELECT targetlist are a bad idea and should be phased
>out.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
