Re: [HACKERS] RETURN QUERY in PL/PgSQL?
2007/5/3, Tom Lane [EMAIL PROTECTED]: Neil Conway [EMAIL PROTECTED] writes: Pavel, my apologies for not getting back to you sooner. On Wed, 2007-25-04 at 07:12 +0200, Pavel Stehule wrote: example: I have table with attr. cust_id, and I want to use parametrized view (table function) where I want to have attr cust_id on output. Hmm, I see your point. I'm personally satisfied with adding a new proargmode to solve this as you suggest. This will break client-side code that looks at proargmode, and I don't think the argument in favor is strong enough to justify that ... can be. But similar changes was more times: named arguments, out, inout attrb .. This depend on application. If any application is written too simply then it can have problem. But which application check proargmodes: pgadmin, phppgadmin, emsmanager, ... it's not frequentation activity. And it's question for maintainers of this applications. What difficult is change it? This syntax is usefull. It lowers risk of name's colisition, and is more readable (if it do what it have to do). I am sorry, but I don't see sense of new table function syntax without changes of proargmodes. Only shortcut for SETOF RECORD isn't usefull. This syntax is standardised, is used in SQL/PSM which PostgreSQL have to adapt this year, or next year, or maybe later, but have to be adapted. And SQL/PSM knows only declared variables or function's parameters. I forgot, it's can be usefull for SQL language procedures. They don't use named arguments (how long?). Regards Pavel Stehule ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
On 4/25/07, Pavel Stehule [EMAIL PROTECTED] wrote: for me RETURNS TABLE (a,b) isn't equialent for (OUT a, OUT b) RETURNS SETOF RECORD, but it's eq. for RETURNS SETOF RECORD ... and SELECT FROM foo() AS (a, b). Reason: example: I have table with attr. cust_id, and I want to use parametrized view (table function) where I want to have attr cust_id on output. You can use table aliases to avoid conflicts. (t.cust_id) -- marko ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
Tom, Pavel, Hmm, I see your point. I'm personally satisfied with adding a new proargmode to solve this as you suggest. This will break client-side code that looks at proargmode, and I don't think the argument in favor is strong enough to justify that ... What kind of client-side code are we talking about breaking? Just pgAdmin phpPgAdmin and the like, or potentially other stuff that calls stored procedures? FWIW, pgAdmin3 1.6 *already* breaks on 8.3 procedures, so we have client-side breakage already. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
Josh Berkus wrote: Tom, Pavel, Hmm, I see your point. I'm personally satisfied with adding a new proargmode to solve this as you suggest. This will break client-side code that looks at proargmode, and I don't think the argument in favor is strong enough to justify that ... What kind of client-side code are we talking about breaking? Just pgAdmin phpPgAdmin and the like, or potentially other stuff that calls stored procedures? FWIW, pgAdmin3 1.6 *already* breaks on 8.3 procedures, so we have client-side breakage already. If we're going to make a change, please do so sooner rather than later. I'd like to take pgAdmin to beta Real Soon Now if possible. Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
Pavel, my apologies for not getting back to you sooner. On Wed, 2007-25-04 at 07:12 +0200, Pavel Stehule wrote: example: I have table with attr. cust_id, and I want to use parametrized view (table function) where I want to have attr cust_id on output. Hmm, I see your point. I'm personally satisfied with adding a new proargmode to solve this as you suggest. RETURN TABLE is specified in std, and it's last statement. Where is RETURN TABLE defined in the standard? The only reference to TABLE I can see is as a multiset value constructor (section 6.39 in the current SQL 200n draft). That would allow RETURN TABLE(...), but it would also allow TABLE(...) to be used in other contexts. I think the right place to implement TABLE(...) per the spec would be in the backend, as part of an implementation of the standard's multiset concept. Therefore, we probably should *not* use RETURN TABLE in PL/PgSQL, since it would induce confusion if we ever do a proper multiset implementation. -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
Neil Conway [EMAIL PROTECTED] writes: Pavel, my apologies for not getting back to you sooner. On Wed, 2007-25-04 at 07:12 +0200, Pavel Stehule wrote: example: I have table with attr. cust_id, and I want to use parametrized view (table function) where I want to have attr cust_id on output. Hmm, I see your point. I'm personally satisfied with adding a new proargmode to solve this as you suggest. This will break client-side code that looks at proargmode, and I don't think the argument in favor is strong enough to justify that ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
On Mon, 2007-04-23 at 17:48 -0400, Tom Lane wrote: I think we've got something isomorphic to that in the patch queue already --- take a look at Pavel's table function patch. It's in need of cleanup but I think it will make it in. Interesting -- I missed that patch, but it seems like a better approach. Are you already reviewing Pavel's patch, or is it something I could take a look at? -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
Hello It is RETURN TABLE(SQL) via ANSI SQL 2003 Table function support is in patch queue: http://archives.postgresql.org/pgsql-patches/2007-02/msg00216.php http://momjian.us/mhonarc/patches/msg1.html Regards Pavel Stehule In a PL/PgSQL set-returning function, returning the result set of a query requires a FOR loop and repeated invocations of the RETURN NEXT statement: FOR x in SELECT ... LOOP RETURN NEXT x; END LOOP; This works, but it seems overly verbose. It occurred to me that we could easily add a new PL/PgSQL statement that evaluates a set-returning expression and adds *all* the resulting rows to the function's result set. For example: RETURN QUERY SELECT ...; I'm not sure of the right name: RETURN ROWS or RETURN ALL might also work. Of course, this is syntax sugar (and superficial sugar at that), but I believe this is a fairly common requirement. _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
Neil Conway [EMAIL PROTECTED] writes: Interesting -- I missed that patch, but it seems like a better approach. Are you already reviewing Pavel's patch, or is it something I could take a look at? The main objection I have is that I don't think changing the definition of pg_proc.proargmodes is a good idea --- that will break some nontrivial amount of client-side code in order to support a distinction that seems unimportant. IMHO anyway. Feel free to take a whack at it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
On Tue, 2007-04-24 at 07:58 +0200, Pavel Stehule wrote: It is RETURN TABLE(SQL) via ANSI SQL 2003 I think there are two basically orthogonal features in the patch: the RETURNS TABLE addition to CREATE FUNCTION, and the RETURN TABLE statement in PL/PgSQL. The former is specified by the SQL standard and is applicable to all PLs, while the latter is syntax sugar for PL/PgSQL. I think it would make sense to split the patch into two separate patches, one for each feature. I'm inclined to agree with Tom that adding PROARGMODE_TABLE isn't worth the trouble: making RETURNS TABLE(...) equivalent to RETURNS SETOF RECORD with OUT parameters strikes me as more elegant. I didn't really understand the name collision argument you made earlier[1]; can you elaborate? Another question is how RETURN NEXT and RETURN TABLE should interact (in PL/PgSQL). I think the two sensible choices are to either disallow a function from using both statements (which is what the patch currently does), or allow both statements to be used, and have RETURN TABLE *not* return from the function -- both RETURN TABLE and RETURN NEXT would append results to the function's result tuplestore. The latter seems more flexible. Do we need the extra set of parentheses in RETURN TABLE? To use one of your earlier examples: CREATE FUNCTION fooff(a int) RETURNS TABLE(a int, b int) AS $$ BEGIN RETURN TABLE(SELECT * FROM Foo WHERE x a); END; $$ LANGUAGE plpgsql; RETURN TABLE SELECT ... ; should be sufficient to allow correct parsing, and is more consistent with the lack of parentheses in the other RETURN variants. -Neil [1] http://archives.postgresql.org/pgsql-patches/2007-04/msg00311.php ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
I think there are two basically orthogonal features in the patch: the RETURNS TABLE addition to CREATE FUNCTION, and the RETURN TABLE statement in PL/PgSQL. The former is specified by the SQL standard and is applicable to all PLs, while the latter is syntax sugar for PL/PgSQL. I think it would make sense to split the patch into two separate patches, one for each feature. it is good idea. I'm inclined to agree with Tom that adding PROARGMODE_TABLE isn't worth the trouble: making RETURNS TABLE(...) equivalent to RETURNS SETOF RECORD with OUT parameters strikes me as more elegant. I didn't really understand the name collision argument you made earlier[1]; can you elaborate? for me RETURNS TABLE (a,b) isn't equialent for (OUT a, OUT b) RETURNS SETOF RECORD, but it's eq. for RETURNS SETOF RECORD ... and SELECT FROM foo() AS (a, b). Reason: example: I have table with attr. cust_id, and I want to use parametrized view (table function) where I want to have attr cust_id on output. Variant a) Tom proposal -- because _cust_id is variable CREATE OR REPLACE FUNCTION foo(arg int) RETURNS TABLE (_cust_id int) AS $$ BEGIN RETURN TABLE (SELECT cust_id FROM tab WHERE some = arg); END; $$ LANGUAGE plpgsql; SELECT * FROM foo(1) as (cust_id); Variant b) My proposal -- cust_id isn't variable CREATE OR REPLACE FUNCTION foo(arg int) RETURNS TABLE (cust_id int) AS $$ BEGIN RETURN TABLE (SELECT cust_id FROM tab WHERE some = arg); END; $$ LANGUAGE plpgsql; SELECT * FROM foo(1); Next argument. I would to use this for SQL/PSM. I didn't find any notice about equality between attributies from RETURNS TABLE clause and OUT variables. If you have TABLE function (RETURNS TABLE) you have to use table expression .. RETURN TABLE(SELECT ...) SQL/PSM doesn't know RETURN NEXT, and if I have accept your argument, then I will be in problems with some implicit variables. I need information, where attribute was used. How plpgsql use variable it is different question. If you want, use table attributes like out variables. plpgsql isn't standardised and then it isn't too important. SQL/PSM is defined, and there is important to difference between TABLE attributies and OUT variables. Another question is how RETURN NEXT and RETURN TABLE should interact (in PL/PgSQL). I think the two sensible choices are to either disallow a function from using both statements (which is what the patch currently does), or allow both statements to be used, and have RETURN TABLE *not* return from the function -- both RETURN TABLE and RETURN NEXT would append results to the function's result tuplestore. The latter seems more flexible. RETURN TABLE is specified in std, and it's last statement. SQL/PSM knows it, and it can be source of problems for beginers in future. Maybe .. RETURN NEXT TABLE Do we need the extra set of parentheses in RETURN TABLE? To use one of your earlier examples: CREATE FUNCTION fooff(a int) RETURNS TABLE(a int, b int) AS $$ BEGIN RETURN TABLE(SELECT * FROM Foo WHERE x a); END; $$ LANGUAGE plpgsql; RETURN TABLE SELECT ... ; should be sufficient to allow correct parsing, and is more consistent with the lack of parentheses in the other RETURN variants. again. std need it, but plpgsql isn't sql/psm language. And it is true, lack of parentheses is more consistent with other plpgsql constructs (not only RETURN statement). -Neil [1] http://archives.postgresql.org/pgsql-patches/2007-04/msg00311.php _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] RETURN QUERY in PL/PgSQL?
In a PL/PgSQL set-returning function, returning the result set of a query requires a FOR loop and repeated invocations of the RETURN NEXT statement: FOR x in SELECT ... LOOP RETURN NEXT x; END LOOP; This works, but it seems overly verbose. It occurred to me that we could easily add a new PL/PgSQL statement that evaluates a set-returning expression and adds *all* the resulting rows to the function's result set. For example: RETURN QUERY SELECT ...; I'm not sure of the right name: RETURN ROWS or RETURN ALL might also work. Of course, this is syntax sugar (and superficial sugar at that), but I believe this is a fairly common requirement. Comments? -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
Neil, This works, but it seems overly verbose. It occurred to me that we could easily add a new PL/PgSQL statement that evaluates a set-returning expression and adds *all* the resulting rows to the function's result set. For example: RETURN QUERY SELECT ...; I'm not sure of the right name: RETURN ROWS or RETURN ALL might also work. Of course, this is syntax sugar (and superficial sugar at that), but I believe this is a fairly common requirement. Comments? Hmmm ... ALL is already a reserved word, so is unlikely to be a variable name, yes? I'd think we could get some breakage on ROWS. So I'd go for RETURN ALL. Overall, I think this is worthwhile, but maybe not enough to bypass feature freeze. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
Neil Conway [EMAIL PROTECTED] writes: This works, but it seems overly verbose. It occurred to me that we could easily add a new PL/PgSQL statement that evaluates a set-returning expression and adds *all* the resulting rows to the function's result set. For example: I think we've got something isomorphic to that in the patch queue already --- take a look at Pavel's table function patch. It's in need of cleanup but I think it will make it in. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RETURN QUERY in PL/PgSQL?
Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: This works, but it seems overly verbose. It occurred to me that we could easily add a new PL/PgSQL statement that evaluates a set-returning expression and adds *all* the resulting rows to the function's result set. For example: I think we've got something isomorphic to that in the patch queue already --- take a look at Pavel's table function patch. It's in need of cleanup but I think it will make it in. Interesting - I haven't followed that one. In pl/perl the equivalent will use one tuplestore on the way in and another on the way out, with return_next() copying between the two. If we had some mechanism like this there is thus a potential for substantial savings, as well as improved clarity. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
