setof record "out" syntax and returning records

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: setof record "out" syntax and returning records
Date: 2008-01-20 20:51:07
Message-ID: 20080120215107.77898b66@webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sebastjan Trepca 2008-01-20 21:01:37 Deadlock in Postgres 8.2
Previous Message David Fetter 2008-01-20 20:35:23 Re: [GENERAL] SHA1 on postgres 8.3

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Smith 2008-01-20 20:55:54 Re: [HACKERS] bgwriter_lru_multiplier blurbs inconsistent
Previous Message Simon Riggs 2008-01-20 20:45:00 Re: Friendly help for psql