Обсуждение: setof record "out" syntax and returning records

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

Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> I thought I could write something like
> create or replace testA(out setof record) as...
> but it seems I can't.

No, you can't.  Write

create or replace testA() returns setof record as...

instead.  "setof" is only allowed in the RETURNS clause --- else we'd
have to figure out what it means to attach "setof" to some OUT
parameters and not others.

            regards, tom lane