Re: TABLE-function patch vs plpgsql

2008/7/30 Hannu Krosing <hannu(at)krosing(dot)net>:
> On Wed, 2008-07-30 at 08:40 +0200, Pavel Stehule wrote:
>> Hello
>>
>> 2008/7/30 Hannu Krosing <hannu(at)krosing(dot)net>:
>> > On Wed, 2008-07-30 at 07:29 +0200, Pavel Stehule wrote:
>> >> 2008/7/29 Hannu Krosing <hannu(at)krosing(dot)net>:
>> >> > On Thu, 2008-07-17 at 19:13 -0400, Tom Lane wrote:
>> >> >> I've been working on the TABLE-function patch, and I am coming to the
>> >> >> conclusion that it's really a bad idea for plpgsql to not associate
>> >> >> variables with output columns --- that is, I think we should make
>> >> >> RETURNS TABLE columns semantically just the same as OUT parameters.
>> >> >
>> >> > I just looked at recent cahnges in pl/python, and found out that RETURNS
>> >> > TABLE is _NOT_ semantically just the same as OUT parameters, at least at
>> >> > API level.
>> >> >
>> >> > Why can't it be ?
>> >> >
>> >> > Why is PROARGMODE_TABLE needed at all ?
>> >>
>> >> because I need to separate classic OUT args from table args.
>> >
>> > I read your explanation, and I still don't understand, why can't TABLE
>> > and SETOF RECORD + OUT args be just different spellings of the same
>> > thing.
>> >
>> > Is there a scenario, where both are needed in the same function ?
>> >
>> >> TABLE function has more clean syntax, then our SRF functions,
>> >
>> > True. But why is separation on C API level needed ?
>>
>> do you know any better way? I need to carry result description, and
>> using proargmodes is natural. In other case I needed add column to
>> pg_proc with result descriptor.
>
> if you need the actual result description for the function, not each
> arg, then the "natural" way would be to keep info about it with function
> (in pg_proc), not wit each arg.

it means new pg_proc column or some new table. With argmode I used
current tools.
>
>> >
>> >> and it isn't
>> >> related only to SQL/PSM. It works nice together with SQL language.
>> >> Actually TABLE variables are exactly same as OUT variables (in
>> >> plpgsq), that is possible, but I am not sure, if it's best too.
>> >
>> > Still I have the same question - What is the difference ?
>> >
>>
>> * remove varname and colname colisions
>> * solve unclean result type rules (one column .. specific type, two
>> and more .. record)
>>
>> >> I have prototype where is possible declare variables derivated from
>> >> function return type
>> >> create function foo(..) returns table(x int, y int) as $$
>> >> declare result foo%rowtype; resx foo.x%type; ....
>> >
>> > I still don't see, why the same thing can't work on
>> >
>> > create function foo(OUT x int, OUT y int) returns setof record as $$
>> > declare result foo%rowtype; resx foo.x%type; ...
>>
>> no it isn't. In this case you has local variables x, y - it's one from
>> typical postgresql bug
>
> ok in pl/pgsql you have local vars.
>

yes, and I have to solve it.

> in pl/python, OUT parameters just define return types (and names if
> returned record is a dict or class).
>
> I have not checked, how pl/perl and pl/tcl do it.
>
> pl/proxy has no notion of local vars.
>

it's problem only in native languages. other PL languages should use
PROARGMODE_TABLE like PROARGMODE_OUT without any changes.

>> create function foo(out x int, out y iny)
>> returns setof record as $$
>> begin
>> for x,y in select x,y from tab loop -- it's wrong!!
>> return next;
>> end loop;
>> ...
>>
>> create function foo(out x int, out y int)
>> returns setof record as $$
>> begin
>> return query select x, y from tab; -- it's wrong too !
>
> does "return query" return a materialized "table" or just cursor ?

it returns materialized table - currently postgsql hasn't executor
node "cursor scan"

>
> that is, can RETURNS TABLE(...) be used to pass around portals ?
>

I haven't idea.

>> >
>> >> all this has to minimalist risk of variables and sql object name collisions.
>> >
>> > Are there any cases, where TABLE functions and OUT + returns SETOF
>> > RECORD functions are _called_ differently ?
>>
>> no
>
> in that case I dare to claim that difference between TABLE functions and
> OUT + returns SETOF RECORD functions is a very pl/pgsql specific thing.
> possibly PL/PSM too, though I don't know if PL/PSM has OUT params
> defined.

It's really pl/pgsql specific, but it's on interface between SQL and
pgsql, and it cannot be solved only for pgsql.

PSM has OUT variables, but only for procedures. You cannot use it in
function. It's postgresql specific feature. What I know, RETURNS TABLE
is one from two ways for table's result. Second way is cursor.

Pavel
>
> -----------------
> Hannu
>
>
>
>