Re: PL/pgSQL question

ycrux(at)club-internet(dot)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 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?

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