Re: TABLE-function patch vs plpgsql - Mailing list pgsql-hackers

On Wed, 2008-07-30 at 08:40 +0200, Pavel Stehule wrote:
> Hello
> 
> 2008/7/30 Hannu Krosing <hannu@krosing.net>:
> > On Wed, 2008-07-30 at 07:29 +0200, Pavel Stehule wrote:
> >> 2008/7/29 Hannu Krosing <hannu@krosing.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.

> >
> >> 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. 

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.

> 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 ? 

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

> >
> >> 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.

-----------------
Hannu