ycrux@xxxxxxxxxxxxxxxx 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
