Re: PL/pgSQL question

ycrux@club-internet.fr wrote:
> 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
typeof 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?

Depends on what column1,column3 are. See the manuals for CREATE TYPE.
If column1 was int4 and column3 was a date you'd do something like:
   CREATE TYPE return_type AS (
     a int4,
     b date
   );

some_type is a variable not a type definition, although you'd probably
define it to be of type "return_type".

Oh, and it should be ... RETURNS SETOF return_type

> 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;

Same difference, but you would change your type definition.

--
   Richard Huxton
   Archonet Ltd