Re: JOIN results of refcursor functions

Milan Oparnica wrote:

>> Then I've tried:
>>
>> CREATE FUNCTION foo(insklid int, out sklid int, out elid INT) RETURNS SETOF record AS $$
>> BEGIN
>> RETURN QUERY SELECT sklid,elid FROM skladkol;
>> RETURN;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> but it returns 5498 rows (which is exact number of rows in that table) but of NULL values. WHAT AM I DOING WRONG ? :(((
>> ....

Pavel Stehule wrote:

> know bug :( - your variable names are in collision with column names.
> You have to protect self - use prefixes for variables
>

Thanks Pavel, fortunately if you select columns by table reference
(table.field) collision is avoided:

CREATE FUNCTION foo(insklid int, out sklid int, out elid INT) RETURNS
SETOF record AS $$
BEGIN
RETURN QUERY SELECT skladkol.sklid, skladkol.elid FROM skladkol;
RETURN;
END;
$$ LANGUAGE plpgsql;

This works fine.

Interesting thing is that using OUT parameters performs much faster than
using SETOF custom composite type when returning large recordsets.

Is this bug coming soon on some to-do-fix-list ?

This structure seems to be nice replacement for PERSISTANT PREPARE I was
posting some months ago, the only mess is out variables position
sensitivity - you must ensure that select statement returns values in
exact order as out parameters are declared.

Best regards,

Milan Oparnica