Thread: TABLE-function patch vs plpgsql
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. Here are some reasons: 1. It's ludicrous to argue that "standards compliance" requires the behavior-as-submitted. plpgsql is not specified by the SQL standard. 2. Not having the parameter names available means that you don't have access to their types either, which is a big problem for polymorphic functions. Read the last couple paragraphs of section 38.3.1: http://developer.postgresql.org/pgdocs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES as well as the following 38.3.2. How would you do those things with a polymorphic TABLE column? 3. Not treating the parameters as assignable variables makes RETURN NEXT nearly worthless in a TABLE function. Since they're not assignable, you can't use the parameterless form of RETURN NEXT (which'd return the current values of the variables). The only alternative available is to return a record or row variable; but there's no convenient way to declare such a variable, since after all the whole point here is that the function's output rowtype is anonymous. 4. It's a whole lot easier to explain things if we can just say that OUT parameters and TABLE parameters work alike. This is especially true when they actually *are* alike for all the other available PLs. If we insist on the current definition then we are eventually going to need to kluge up some solutions to #2 and #3, which seems like make-work to me when we already have smooth solutions to these problems for OUT parameters. Comments? For the archives, here is the patch as I currently have it (with the no-plpgsql-variables behavior). But unless I hear a good argument to the contrary, I'm going to change that part before committing. regards, tom lane
Attachment
Hello The core of problems is in standard that doesn't know RETURN NEXT statement and knows only RETURN TABLE statement - so PL/pgPSM or SQL doesn't have problem. I am not sure about PL/pgSQL, but I thing so using TABLE attribs as OUT variables is maybe too simple solution - there isn't any progress to current state, and where OUT variables are typically source of mistakes. Maybe we can use some well defined implicit record, maybe NEW (or RESULT, ROW_RESULT, ROW, TABLE_ROW, ...) like trigger - some like create or replace function foo(i int) returns table(a int, b int) as $$ begin for j in 1..i loop new.a := j; new.b := j+1; return next new; -- maybe only RETURN NEXT??? end loop; end; $$ language plpgsql Regards Pavel Stehule 2008/7/18 Tom Lane <tgl@sss.pgh.pa.us>: > 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. > Here are some reasons: > > 1. It's ludicrous to argue that "standards compliance" requires the > behavior-as-submitted. plpgsql is not specified by the SQL standard. > > 2. Not having the parameter names available means that you don't have > access to their types either, which is a big problem for polymorphic > functions. Read the last couple paragraphs of section 38.3.1: > http://developer.postgresql.org/pgdocs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES > as well as the following 38.3.2. How would you do those things with > a polymorphic TABLE column? > > 3. Not treating the parameters as assignable variables makes RETURN NEXT > nearly worthless in a TABLE function. Since they're not assignable, > you can't use the parameterless form of RETURN NEXT (which'd return > the current values of the variables). The only alternative available > is to return a record or row variable; but there's no convenient way > to declare such a variable, since after all the whole point here is > that the function's output rowtype is anonymous. > > 4. It's a whole lot easier to explain things if we can just say that > OUT parameters and TABLE parameters work alike. This is especially > true when they actually *are* alike for all the other available PLs. > > If we insist on the current definition then we are eventually going to > need to kluge up some solutions to #2 and #3, which seems like make-work > to me when we already have smooth solutions to these problems for > OUT parameters. > > Comments? > > For the archives, here is the patch as I currently have it (with the > no-plpgsql-variables behavior). But unless I hear a good argument > to the contrary, I'm going to change that part before committing. > > regards, tom lane > >
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> Maybe we can use some well defined implicit record, maybe NEW (or
> RESULT, ROW_RESULT, ROW, TABLE_ROW, ...) like trigger - some like
That sounds like exactly the sort of kluge-solution that I didn't
want to get involved with ...
Anyway, the core feature is in, and we still have several months
before 8.4 feature freeze to debate how plpgsql ought to interact
with it.
regards, tom lane
2008/7/18 Tom Lane <tgl@sss.pgh.pa.us>: > "Pavel Stehule" <pavel.stehule@gmail.com> writes: >> Maybe we can use some well defined implicit record, maybe NEW (or >> RESULT, ROW_RESULT, ROW, TABLE_ROW, ...) like trigger - some like > > That sounds like exactly the sort of kluge-solution that I didn't > want to get involved with ... > > Anyway, the core feature is in, and we still have several months > before 8.4 feature freeze to debate how plpgsql ought to interact > with it. > I agree. Regards p.s. other solution - using referenced types declare foorec fcename%ROWTYPE -- allows only current fce name fooscalar fcename.col%TYPE regards Pavel Stehule and many thanks for commit this patch > regards, tom lane >
On 7/18/08, Tom Lane <tgl@sss.pgh.pa.us> 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. > Here are some reasons: > > 1. It's ludicrous to argue that "standards compliance" requires the > behavior-as-submitted. plpgsql is not specified by the SQL standard. Yes, but it would be a good feature addition to plpgsql. Currently there is no way to suppress the local variable creation. The proposed behaviour would give that possibility. > 2. Not having the parameter names available means that you don't have > access to their types either, which is a big problem for polymorphic > functions. Read the last couple paragraphs of section 38.3.1: > http://developer.postgresql.org/pgdocs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES > as well as the following 38.3.2. How would you do those things with > a polymorphic TABLE column? This does not make sense as Postgres does not support polymorphic table columns... For polymorphic function arguments user should use OUT parameters. I think thats the point - it should not be just syntactic sugar for OUT parameters, let it be different. > 3. Not treating the parameters as assignable variables makes RETURN NEXT > nearly worthless in a TABLE function. Since they're not assignable, > you can't use the parameterless form of RETURN NEXT (which'd return > the current values of the variables). The only alternative available > is to return a record or row variable; but there's no convenient way > to declare such a variable, since after all the whole point here is > that the function's output rowtype is anonymous. > > 4. It's a whole lot easier to explain things if we can just say that > OUT parameters and TABLE parameters work alike. This is especially > true when they actually *are* alike for all the other available PLs. What other PLs do create local variables for OUT parameters? > If we insist on the current definition then we are eventually going to > need to kluge up some solutions to #2 and #3, which seems like make-work > to me when we already have smooth solutions to these problems for > OUT parameters. > > Comments? I would prefer - no local vars, no polymorphism and funcname%rowtype. Some functions are better written with OUT parameters but some with record variable for return rows. The new behaviour would allow picking best coding style for a function. -- marko
"Marko Kreen" <markokr@gmail.com> writes:
> On 7/18/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 1. It's ludicrous to argue that "standards compliance" requires the
>> behavior-as-submitted. plpgsql is not specified by the SQL standard.
> Yes, but it would be a good feature addition to plpgsql.
> Currently there is no way to suppress the local variable
> creation. The proposed behaviour would give that possibility.
Why would anyone consider that a "feature"?
>> 2. Not having the parameter names available means that you don't have
>> access to their types either, which is a big problem for polymorphic
>> functions.
> This does not make sense as Postgres does not support
> polymorphic table columns...
No, but it certainly supports polymorphic function output parameters,
and that's what these really are.
> I think thats the point - it should not be just syntactic sugar for
> OUT parameters, let it be different.
Why? All you're doing is proposing that we deliberately cripple
the semantics.
regards, tom lane
On 7/18/08, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Marko Kreen" <markokr@gmail.com> writes: > > On 7/18/08, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > >> 1. It's ludicrous to argue that "standards compliance" requires the > >> behavior-as-submitted. plpgsql is not specified by the SQL standard. > > > Yes, but it would be a good feature addition to plpgsql. > > Currently there is no way to suppress the local variable > > creation. The proposed behaviour would give that possibility. > > Why would anyone consider that a "feature"? Well, it's issue of "big global namespace" vs. "several local namespaces" If you have function that has lot of OUT parameters and also local variables it gets confusing fast. It would be good to avoid OUT parameters polluting local variable space. > >> 2. Not having the parameter names available means that you don't have > >> access to their types either, which is a big problem for polymorphic > >> functions. > > > This does not make sense as Postgres does not support > > polymorphic table columns... > > No, but it certainly supports polymorphic function output parameters, > and that's what these really are. I was referring to the syntax of the feature: "RETURNS TABLE" > > I think thats the point - it should not be just syntactic sugar for > > OUT parameters, let it be different. > > Why? All you're doing is proposing that we deliberately cripple > the semantics. plpgsql already is rather crippled, we could use that feature to give additional flexibility to it. -- marko
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 ? > 4. It's a whole lot easier to explain things if we can just say that > OUT parameters and TABLE parameters work alike. This is especially > true when they actually *are* alike for all the other available PLs. It would be nice, if they were the same at API level as well. -------------------- Hannu
Hannu Krosing <hannu@krosing.net> writes:
> Why is PROARGMODE_TABLE needed at all ?
Personally I would rather not have it, but Pavel insists it's needed
for standards compliance in PL/PSM, where output TABLE columns are not
supposed to have names visible within the function.
One reason to have it is so we can distinguish the correct way to
reverse-list an output parameter (as OUT or as a table result column).
Although we could equally well solve that with an extra bool column in
pg_proc instead of redefining proargmodes, as long as you're willing to
accept the reasonable restriction that you can't mix the two styles of
declaring output parameters. In principle PL/PSM could look at such a
bool too, so there's more than one way to do it.
The feeling I had about it was that if we were adding
PROARGMODE_VARIADIC in 8.4 then there wasn't any very strong argument
not to add PROARGMODE_TABLE; any code looking at proargmodes is going
to need updates anyway.
regards, tom lane
<div dir="ltr">Tom> RETURNS TABLE columns semantically just the same as OUT parameters.<br /><br />I hope you are notproposing to create another case of crippled OUT parameters that are quite problematic to use together with inline sqlor has it gotten fixed on the road (we are still using 8.2 on most of our servers).<br /><br /><div class="gmail_quote">OnFri, Jul 18, 2008 at 2:13 AM, Tom Lane <span dir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span>wrote:<br /><blockquote class="gmail_quote" style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> I've been working on theTABLE-function patch, and I am coming to the<br /> conclusion that it's really a bad idea for plpgsql to not associate<br/> variables with output columns --- that is, I think we should make<br /> RETURNS TABLE columns semanticallyjust the same as OUT parameters.<br /> Here are some reasons:<br /><br /> 1. It's ludicrous to argue that "standardscompliance" requires the<br /> behavior-as-submitted. plpgsql is not specified by the SQL standard.<br /><br />2. Not having the parameter names available means that you don't have<br /> access to their types either, which is a bigproblem for polymorphic<br /> functions. Read the last couple paragraphs of section 38.3.1:<br /><a href="http://developer.postgresql.org/pgdocs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES" target="_blank">http://developer.postgresql.org/pgdocs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES</a><br />as well as the following <a href="http://38.3.2." target="_blank">38.3.2.</a> How would you do those things with<br />a polymorphic TABLE column?<br /><br /> 3. Not treating the parameters as assignable variables makes RETURN NEXT<br />nearly worthless in a TABLE function. Since they're not assignable,<br /> you can't use the parameterless form of RETURNNEXT (which'd return<br /> the current values of the variables). The only alternative available<br /> is to returna record or row variable; but there's no convenient way<br /> to declare such a variable, since after all the wholepoint here is<br /> that the function's output rowtype is anonymous.<br /><br /> 4. It's a whole lot easier to explainthings if we can just say that<br /> OUT parameters and TABLE parameters work alike. This is especially<br /> truewhen they actually *are* alike for all the other available PLs.<br /><br /> If we insist on the current definition thenwe are eventually going to<br /> need to kluge up some solutions to #2 and #3, which seems like make-work<br /> to mewhen we already have smooth solutions to these problems for<br /> OUT parameters.<br /><br /> Comments?<br /><br /> Forthe archives, here is the patch as I currently have it (with the<br /> no-plpgsql-variables behavior). But unless I heara good argument<br /> to the contrary, I'm going to change that part before committing.<br /><br /> regards, tom lane<br /><br /><br /><br /> --<br /> Sent via pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers" target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/><br /></blockquote></div><br /></div>
On Tue, 2008-07-29 at 12:46 -0400, Tom Lane wrote: > Hannu Krosing <hannu@krosing.net> writes: > > Why is PROARGMODE_TABLE needed at all ? > > Personally I would rather not have it, but Pavel insists it's needed > for standards compliance in PL/PSM, where output TABLE columns are not > supposed to have names visible within the function. Why not just hide the names from PL/PSM ? The current way seems to just add complexity for no good reason. > One reason to have it is so we can distinguish the correct way to > reverse-list an output parameter (as OUT or as a table result column). > Although we could equally well solve that with an extra bool column in > pg_proc instead of redefining proargmodes, as long as you're willing to > accept the reasonable restriction that you can't mix the two styles of > declaring output parameters. Can you mix them with current API ? what would that mean ? I mean, does the _caller_ ofd the function need to distinguisd OUT and TABLE returns ? > In principle PL/PSM could look at such a > bool too, so there's more than one way to do it. Yup, I'd prefer that. > The feeling I had about it was that if we were adding > PROARGMODE_VARIADIC in 8.4 then there wasn't any very strong argument > not to add PROARGMODE_TABLE; any code looking at proargmodes is going > to need updates anyway. I missed the addition PROARGMODE_VARIADIC too. Has it already been added ? What is it supposed to do ? ---------------- Hannu
Hannu Krosing <hannu@krosing.net> writes: > On Tue, 2008-07-29 at 12:46 -0400, Tom Lane wrote: >> The feeling I had about it was that if we were adding >> PROARGMODE_VARIADIC in 8.4 then there wasn't any very strong argument >> not to add PROARGMODE_TABLE; any code looking at proargmodes is going >> to need updates anyway. > I missed the addition PROARGMODE_VARIADIC too. > Has it already been added ? > What is it supposed to do ? http://archives.postgresql.org/pgsql-committers/2008-07/msg00127.php regards, tom lane
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. TABLE function has more clean syntax, then our SRF functions, 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. 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; .... all this has to minimalist risk of variables and sql object name collisions. Regards Pavel Stehule > >> 4. It's a whole lot easier to explain things if we can just say that >> OUT parameters and TABLE parameters work alike. This is especially >> true when they actually *are* alike for all the other available PLs. > > It would be nice, if they were the same at API level as well. > > -------------------- > Hannu > >
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 ? > 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 ? > 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; ... > 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 ? ------------------ Hannu
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. > >> 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 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 ! > >> 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 Regards Pavel Stehule > > ------------------ > Hannu > > >
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
2008/7/30 Hannu Krosing <hannu@krosing.net>: > 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. 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 > > > >