Re: [GENERAL] 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(at)webthatworks(dot)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

This thread made me try this example. Note that there's a WARNING in the spot
where I pressed tab.

alvherre=# create or replace function foo (out pg_class) language plpgsql as $$ declare i pg_class%rowtype; begin for i in select * from pg_class loop return next; end loop; end; $$;
ERROR: cannot use RETURN NEXT in a non-SETOF function en o cerca de «next»
LINEA 1: ...begin for i in select * from pg_class loop return next; end ...
^
alvherre=# set lc_messWARNING: problem in alloc set PL/PgSQL function context: detected write past chunk end in block 0xb541d0, chunk 0xb562c0
ages to 'C';
WARNING: problem in alloc set PL/PgSQL function context: detected write past chunk en
d in block 0xb541d0, chunk 0xb562c0
SET

The reason I tried to change lc_messages is that the spanish message
struck me as a bit odd and wanted to see what the english message looked
like.

The full sequence required to create the message in a clean backend, regardless
of whether the foo(pg_class) function exists previously, is:

create function foo (out pg_class) language plpgsql as $$ declare i pg_class%type; begin for i in select * from pg_class loop return next; end loop; end; $$;
create or replace function foo (out pg_class) language plpgsql as $$ declare i pg_class%type; begin for i in select * from pg_class loop return next; end loop; end; $$;
create or replace function foo (out pg_class) language plpgsql as $$ declare i pg_class%rowtype; begin for i in select * from pg_class loop return next; end loop ; end; $$;
set lc_mess <tab>

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> alvherre=# set lc_messWARNING: problem in alloc set PL/PgSQL function context: detected write past chunk end in block 0xb541d0, chunk 0xb562c0
> ages to 'C';
> WARNING: problem in alloc set PL/PgSQL function context: detected write past chunk en
> d in block 0xb541d0, chunk 0xb562c0
> SET

Ugh. I could not reproduce this here though; there's some missing bit
of context. Are you testing CVS HEAD? I assume you've got --enable-nls
on, but what were the initial settings of lc_messages, the server
locale, and the database encoding?

regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > alvherre=# set lc_messWARNING: problem in alloc set PL/PgSQL function context: detected write past chunk end in block 0xb541d0, chunk 0xb562c0
> > ages to 'C';
> > WARNING: problem in alloc set PL/PgSQL function context: detected write past chunk en
> > d in block 0xb541d0, chunk 0xb562c0
> > SET
>
> Ugh. I could not reproduce this here though; there's some missing bit
> of context. Are you testing CVS HEAD? I assume you've got --enable-nls
> on, but what were the initial settings of lc_messages, the server
> locale, and the database encoding?

False alarm, I think. I cleaned and rebuilt and now I can't reproduce it.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> False alarm, I think. I cleaned and rebuilt and now I can't reproduce it.

Strange. Most of the time I'd have no problem writing this off as a
build-synchronization error, but the tree has been so quiet lately due
to the release cycle that this seems an odd time to be seeing such a
problem. I did a quick look through the CVS logs and couldn't find any
plausible-looking causes since the start of the year; except maybe this:

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/include/access/relscan.h.diff?r1=1.59;r2=1.60;f=h

but alignment rules should have meant that that didn't really move any
of the other fields.

Anyway, if it went away on a clean rebuild, seems that a stale .o file
must be the explanation. Do you normally use --enable-depend?

regards, tom lane

Tom Lane wrote:

> Strange. Most of the time I'd have no problem writing this off as a
> build-synchronization error, but the tree has been so quiet lately due
> to the release cycle that this seems an odd time to be seeing such a
> problem.

This was on a laptop I hadn't used in some time, and I think I must have
done "make install" in, for example, src/backend only. Normally, I use
my do-it-all script which zaps the entire install tree, but sometimes I
don't.

> Anyway, if it went away on a clean rebuild, seems that a stale .o file
> must be the explanation. Do you normally use --enable-depend?

Yes.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> Strange. Most of the time I'd have no problem writing this off as a
>> build-synchronization error, but the tree has been so quiet lately due
>> to the release cycle that this seems an odd time to be seeing such a
>> problem.

> This was on a laptop I hadn't used in some time, and I think I must have
> done "make install" in, for example, src/backend only.

Ah. That could explain it, if you had a plpgsql.so that was quite out
of date compared to the backend. OK, then I'm prepared to forget about
this one ...

regards, tom lane