I've already read this
http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html#extended
but I still can't get it clear.
I thought I could write something like
create or replace testA(out setof record) as...
but it seems I can't. Or am I missing something?
then I wrote something like
create or replace testA() returns setof record as
$$
declare
_row record;
_cursor refcursor;
begin
open _cursor for select colA, colB from table;
loop
fetch _cursor into_row;
exit when not found;
return next _row;
end loop;
close _cursor;
return;
end;
$$ language plpgsql;
but then I've to specify the type of column in
select * from testA() as (...);
Shouldn't be the type of column known since they come from a select?
Does the problem comes from the fact I could change the cursor
definition at each call of the function?
What if I'd like to avoid code duplication (defining returned types
in more than one place)?
In the example the returned type are still declared in 2 places (the
table where they come from and the function signature):
create or replace testA(out col1 int, out col2 text...) returns setof
record as $$
...
Yeah I know there is no difference from any other function (even C
functions) but when the column you're returning back start to be
more than 3 it is a bit of a pain (read error prone).
Declaring a composite type looks even more painful just to
encapsulate simple queries [1].
myrow tablename%ROWTYPE;
could be an option.
Does it works on views too?
Any other way to return recordset from functions?
Just to know the options...
[1] I'm thinking to encapsulate them because I foresee they won't be
simple forever... and if they are encapsulated now I won't have to
change the client code later. I just would like to have an idea of
the cost of doing it now.
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
