Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
(oops this mail never reached out, it seems, resending) "Kevin Grittner" writes: > Tom Lane wrote: >> Yeah, I've come round to that position too. I think allowing >> parameter names to be checked only after query names is probably >> the best answer. > > That seems the most useful and least surprising approach to me. Allow me to disagree. I don't want to recheck all my SQL functions whenever I ALTER TABLE … ADD|DROP|RENAME COLUMN …; We don't even have a nice way to list functions dependencies towards objects that are part of their body to help me out here, or do we? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Mon, Apr 18, 2011 at 11:33 AM, Alvaro Herrera wrote: > Excerpts from Robert Haas's message of lun abr 18 09:33:06 -0300 2011: > >> I don't much like Jim's syntax suggestion (the alias really ought to >> be declared within the function body, I think, not added to the CREATE >> FUNCTION statement) but I don't necessarily think it's a bad idea. >> What would be even better, in my view, is having a short alias that is >> defined by default, but all previous proposals in this vein have been >> shot down by Tom and Andrew. As a practical matter, though, I think >> what Jim is talking about speaks to a real need - people want to make >> SQL function names long and descriptive, but they do NOT want to spell >> out that long function name 16 times inside the function body. > > plpgsql has the #option thing in functions; why can't we have something > similar in SQL functions? > > CREATE FUNCTION function_with_really_really_descriptive_name ( > some_parameter int > ) RETURNS int LANGUAGE SQL AS $$ > #option function_alias fwrrdn > SELECT fwrrdn.some_parameter > $$; > > Not necessarily that exact syntax. If we are rejecting $foo on grounds of deviating from sql standard, shouldn't this be rejected on the same grounds? There is no such syntax in sql/psm. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Excerpts from Robert Haas's message of lun abr 18 09:33:06 -0300 2011: > I don't much like Jim's syntax suggestion (the alias really ought to > be declared within the function body, I think, not added to the CREATE > FUNCTION statement) but I don't necessarily think it's a bad idea. > What would be even better, in my view, is having a short alias that is > defined by default, but all previous proposals in this vein have been > shot down by Tom and Andrew. As a practical matter, though, I think > what Jim is talking about speaks to a real need - people want to make > SQL function names long and descriptive, but they do NOT want to spell > out that long function name 16 times inside the function body. plpgsql has the #option thing in functions; why can't we have something similar in SQL functions? CREATE FUNCTION function_with_really_really_descriptive_name ( some_parameter int ) RETURNS int LANGUAGE SQL AS $$ #option function_alias fwrrdn SELECT fwrrdn.some_parameter $$; Not necessarily that exact syntax. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Thu, Apr 14, 2011 at 10:56 PM, Pavel Stehule wrote: > 2011/4/15 Jim Nasby : >> On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote: >>> Tom Lane wrote: Robert Haas writes: >>> > So far the most promising proposal I've seen seems to be to let > id mean the parameter called id only when it can't refer to > anything in the query. >>> Yeah, I've come round to that position too. I think allowing parameter names to be checked only after query names is probably the best answer. >>> >>> +1 >>> >>> That seems the most useful and least surprising approach to me. >> >> As part of this, can we also allow specifying an alias for the function >> name? That would make it far less onerous to disambiguate parameters. >> Unfortunately we obviously couldn't use AS as the keyword for this alias; >> maybe we could use ALIAS instead? IE: >> >> CREATE FUNCTION function_with_really_really_descriptive_name ( >> some_parameter int >> ) RETURNS int LANGUAGE SQL ALIAS fwrrdn AS $$ >> SELECT fwrrdn.some_parameter >> $$; >> -- > > I see this can be problem for other languages - mainly for PLpgSQL. > There should be aliases supported too. And this small feature can be > terible when somebody will try to port your code to other platforms. > Personally I am thinking, so it isn't necessary > > -1 I don't much like Jim's syntax suggestion (the alias really ought to be declared within the function body, I think, not added to the CREATE FUNCTION statement) but I don't necessarily think it's a bad idea. What would be even better, in my view, is having a short alias that is defined by default, but all previous proposals in this vein have been shot down by Tom and Andrew. As a practical matter, though, I think what Jim is talking about speaks to a real need - people want to make SQL function names long and descriptive, but they do NOT want to spell out that long function name 16 times inside the function body. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/4/15 Jim Nasby : > On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote: >> Tom Lane wrote: >>> Robert Haas writes: >> So far the most promising proposal I've seen seems to be to let id mean the parameter called id only when it can't refer to anything in the query. >> >>> Yeah, I've come round to that position too. I think allowing >>> parameter names to be checked only after query names is probably >>> the best answer. >> >> +1 >> >> That seems the most useful and least surprising approach to me. > > As part of this, can we also allow specifying an alias for the function name? > That would make it far less onerous to disambiguate parameters. Unfortunately > we obviously couldn't use AS as the keyword for this alias; maybe we could > use ALIAS instead? IE: > > CREATE FUNCTION function_with_really_really_descriptive_name ( > some_parameter int > ) RETURNS int LANGUAGE SQL ALIAS fwrrdn AS $$ > SELECT fwrrdn.some_parameter > $$; > -- I see this can be problem for other languages - mainly for PLpgSQL. There should be aliases supported too. And this small feature can be terible when somebody will try to port your code to other platforms. Personally I am thinking, so it isn't necessary -1 Regards Pavel Stehule > Jim C. Nasby, Database Architect j...@nasby.net > 512.569.9461 (cell) http://jim.nasby.net > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote: > Tom Lane wrote: >> Robert Haas writes: > >>> So far the most promising proposal I've seen seems to be to let >>> id mean the parameter called id only when it can't refer to >>> anything in the query. > >> Yeah, I've come round to that position too. I think allowing >> parameter names to be checked only after query names is probably >> the best answer. > > +1 > > That seems the most useful and least surprising approach to me. As part of this, can we also allow specifying an alias for the function name? That would make it far less onerous to disambiguate parameters. Unfortunately we obviously couldn't use AS as the keyword for this alias; maybe we could use ALIAS instead? IE: CREATE FUNCTION function_with_really_really_descriptive_name ( some_parameter int ) RETURNS int LANGUAGE SQL ALIAS fwrrdn AS $$ SELECT fwrrdn.some_parameter $$; -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Tom Lane wrote: > Robert Haas writes: >> So far the most promising proposal I've seen seems to be to let >> id mean the parameter called id only when it can't refer to >> anything in the query. > Yeah, I've come round to that position too. I think allowing > parameter names to be checked only after query names is probably > the best answer. +1 That seems the most useful and least surprising approach to me. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas writes: > On Thu, Apr 14, 2011 at 1:55 PM, Tom Lane wrote: >> Hmm, what I read Dimitri to be proposing is that we *require* parameter >> names to be qualified with the function name. I don't recall hearing >> that before. It would solve the problem perhaps, but I think the moans >> and groans will be numerous. > So far the most promising proposal I've seen seems to be to let id > mean the parameter called id only when it can't refer to anything in > the query. > Tabula raza, I'd prefer your proposal to make any ambiguity an error, > but it's not worth the breakage. Yeah, I've come round to that position too. I think allowing parameter names to be checked only after query names is probably the best answer. > I'd be fine with having a way to > explicitly request that behavior though, a la Perl's "use strict". This is possible but it's not clear it's worth the work. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Thu, Apr 14, 2011 at 1:55 PM, Tom Lane wrote: > Robert Haas writes: >> On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine >> wrote: >>> Unless we make it so that no such version ever exists. Meaning that the >>> code works fine as is or using WHERE id = developer_lookup.id. AS id >>> can't ever be the parameter in this case, you're just fine. >>> >>> Bearing in mind that $1 etc shortcuts still are available, I don't >>> really see this qualification of parameter names with function names so >>> big a problem that we should find a way to avoid it and risk breaking >>> compatibility. >>> >>> Don't forget that any ambiguity here will mean *huge* migration costs. > >> If I'm reading your email correctly, we're in agreement. > > Hmm, what I read Dimitri to be proposing is that we *require* parameter > names to be qualified with the function name. I don't recall hearing > that before. It would solve the problem perhaps, but I think the moans > and groans will be numerous. So far the most promising proposal I've seen seems to be to let id mean the parameter called id only when it can't refer to anything in the query. Tabula raza, I'd prefer your proposal to make any ambiguity an error, but it's not worth the breakage. I'd be fine with having a way to explicitly request that behavior though, a la Perl's "use strict". -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas writes: > On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine > wrote: >> Unless we make it so that no such version ever exists. Meaning that the >> code works fine as is or using WHERE id = developer_lookup.id. AS id >> can't ever be the parameter in this case, you're just fine. >> >> Bearing in mind that $1 etc shortcuts still are available, I don't >> really see this qualification of parameter names with function names so >> big a problem that we should find a way to avoid it and risk breaking >> compatibility. >> >> Don't forget that any ambiguity here will mean *huge* migration costs. > If I'm reading your email correctly, we're in agreement. Hmm, what I read Dimitri to be proposing is that we *require* parameter names to be qualified with the function name. I don't recall hearing that before. It would solve the problem perhaps, but I think the moans and groans will be numerous. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine wrote: > Robert Haas writes: >> rhaas=# CREATE OR REPLACE FUNCTION developer_lookup(id integer) >> RETURNS text AS $$SELECT name FROM developer WHERE id = $1$$ LANGUAGE >> sql STABLE; >> >> Now, when this person attempts to recreate this function on a >> hypothetical version of PostgreSQL that thinks "id" is ambiguous, it >> doesn't work. > > Unless we make it so that no such version ever exists. Meaning that the > code works fine as is or using WHERE id = developer_lookup.id. AS id > can't ever be the parameter in this case, you're just fine. > > Bearing in mind that $1 etc shortcuts still are available, I don't > really see this qualification of parameter names with function names so > big a problem that we should find a way to avoid it and risk breaking > compatibility. > > Don't forget that any ambiguity here will mean *huge* migration costs. If I'm reading your email correctly, we're in agreement. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas writes: > rhaas=# CREATE OR REPLACE FUNCTION developer_lookup(id integer) > RETURNS text AS $$SELECT name FROM developer WHERE id = $1$$ LANGUAGE > sql STABLE; > > Now, when this person attempts to recreate this function on a > hypothetical version of PostgreSQL that thinks "id" is ambiguous, it > doesn't work. Unless we make it so that no such version ever exists. Meaning that the code works fine as is or using WHERE id = developer_lookup.id. AS id can't ever be the parameter in this case, you're just fine. Bearing in mind that $1 etc shortcuts still are available, I don't really see this qualification of parameter names with function names so big a problem that we should find a way to avoid it and risk breaking compatibility. Don't forget that any ambiguity here will mean *huge* migration costs. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/4/9 Tom Lane : > Robert Haas writes: >> On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus wrote: >>> Hence the GUC. Where's the issue? > >> Behavior-changing GUCs for this kind of thing cause a lot of problems. >> If you need one GUC setting for your application to work, and the >> extension you have installed needs the other setting, you're screwed. >> In the worst case, if a security-definer function is involved, you can >> create a security hole, for example by convincing the system that id = >> $1 is intended to mean $1 = $1, or some such. You can of course >> attach the GUC settings to each individual function, but that doesn't >> really work either unless you do it for every function in the system. >> The fundamental problem here is that GUCs are dynamically scoped, >> while this problem is lexically scoped. > > Yeah. In the plpgsql case, we did make provisions to control the > behavior per-function. In principle we could do the same for SQL > functions, but it'd be rather a PITA I think. (In particular, the "easy > way out" of attaching SET clauses to the functions would be a bad idea > because it would defeat inlining.) what about a new language like SQLc? - like SQL compatibility. pg_upgrade can move old code into this compatibility language when detect some posible problems. Pavel > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas writes: > On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus wrote: >> Hence the GUC. Where's the issue? > Behavior-changing GUCs for this kind of thing cause a lot of problems. > If you need one GUC setting for your application to work, and the > extension you have installed needs the other setting, you're screwed. > In the worst case, if a security-definer function is involved, you can > create a security hole, for example by convincing the system that id = > $1 is intended to mean $1 = $1, or some such. You can of course > attach the GUC settings to each individual function, but that doesn't > really work either unless you do it for every function in the system. > The fundamental problem here is that GUCs are dynamically scoped, > while this problem is lexically scoped. Yeah. In the plpgsql case, we did make provisions to control the behavior per-function. In principle we could do the same for SQL functions, but it'd be rather a PITA I think. (In particular, the "easy way out" of attaching SET clauses to the functions would be a bad idea because it would defeat inlining.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus wrote: >> Now, when this person attempts to recreate this function on a >> hypothetical version of PostgreSQL that thinks "id" is ambiguous, it >> doesn't work. > > Hence the GUC. Where's the issue? Behavior-changing GUCs for this kind of thing cause a lot of problems. If you need one GUC setting for your application to work, and the extension you have installed needs the other setting, you're screwed. In the worst case, if a security-definer function is involved, you can create a security hole, for example by convincing the system that id = $1 is intended to mean $1 = $1, or some such. You can of course attach the GUC settings to each individual function, but that doesn't really work either unless you do it for every function in the system. The fundamental problem here is that GUCs are dynamically scoped, while this problem is lexically scoped. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
> Now, when this person attempts to recreate this function on a > hypothetical version of PostgreSQL that thinks "id" is ambiguous, it > doesn't work. Hence the GUC. Where's the issue? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Fri, Apr 8, 2011 at 3:56 PM, Josh Berkus wrote: >> But breaking people's code is not a better answer. We still >> have people on 8.2 because the pain of upgrading to 8.3 is more than >> they can bear, and how many releases have we spent trying to get >> standard_conforming_strings worked out? I admit this probably >> wouldn't be as bad, but we've managed to put out several releases in a >> row now that are relatively painless to upgrade between, and I think >> that's a trend we should try to keep going. > > I guess I'm not understanding the backwards compatibility problem. I've > looked up the thread, and I still don't see a real-world issue. If we > (by default) throw an error on ambiguity, and have GUC to turn that off > (in which case, it resolves column-first), I really don't see what > problem anyone could have upgrading. > > Can you explain it to me? Consider: rhaas=# CREATE TABLE developer (id serial primary key, name text not null); NOTICE: CREATE TABLE will create implicit sequence "developer_id_seq" for serial column "developer.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "developer_pkey" for table "developer" CREATE TABLE rhaas=# insert into developer (name) values ('Tom'), ('Bruce'); INSERT 0 2 rhaas=# CREATE OR REPLACE FUNCTION developer_lookup(id integer) RETURNS text AS $$SELECT name FROM developer WHERE id = $1$$ LANGUAGE sql STABLE; CREATE FUNCTION rhaas=# SELECT developer_lookup(1); developer_lookup -- Tom (1 row) Now, when this person attempts to recreate this function on a hypothetical version of PostgreSQL that thinks "id" is ambiguous, it doesn't work. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
> But breaking people's code is not a better answer. We still > have people on 8.2 because the pain of upgrading to 8.3 is more than > they can bear, and how many releases have we spent trying to get > standard_conforming_strings worked out? I admit this probably > wouldn't be as bad, but we've managed to put out several releases in a > row now that are relatively painless to upgrade between, and I think > that's a trend we should try to keep going. I guess I'm not understanding the backwards compatibility problem. I've looked up the thread, and I still don't see a real-world issue. If we (by default) throw an error on ambiguity, and have GUC to turn that off (in which case, it resolves column-first), I really don't see what problem anyone could have upgrading. Can you explain it to me? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Merlin Moncure writes: > On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane wrote: >> Well, if we're going to consider 100% backwards compatibility a "must", >> then we should just stick with what the submitted patch does, ie, >> unqualified names are matched first to query columns, and to parameters >> only if there's no column match. This is also per spec if I interpreted >> Peter's comments correctly. The whole thread started because I >> suggested that throwing an error for ambiguous cases might be a better >> design in the long run, but apparently long term ease of code >> maintenance is far down our list of priorities ... > +1, as long as you are 100.0% sure this is not going to break any > existing code. For example, what happens if the argument is named the > same as a table? I was a bit sloppy in my statement above --- what the code is actually doing (or should be doing) is matching to parameters only after the core parser fails to find any match. So "unqualified reference to whole-row" would take precedence too. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan wrote: >>> That doesn't mean we should arbitrarily break compatibility with pl/sql, nor >>> that we should feel free to add on warts such as $varname that are >>> completely at odds with the style of the rest of the language. That doesn't >>> do anything except produce a mess. > >> Well, what it does is avoid breaking compatibility with previous >> versions of PostgreSQL. I think that actually does have some value. >> Otherwise, we'd be folding to upper-case by default. > > Well, if we're going to consider 100% backwards compatibility a "must", > then we should just stick with what the submitted patch does, ie, > unqualified names are matched first to query columns, and to parameters > only if there's no column match. This is also per spec if I interpreted > Peter's comments correctly. The whole thread started because I > suggested that throwing an error for ambiguous cases might be a better > design in the long run, but apparently long term ease of code > maintenance is far down our list of priorities ... +1, as long as you are 100.0% sure this is not going to break any existing code. For example, what happens if the argument is named the same as a table? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Fri, Apr 8, 2011 at 11:51 AM, David E. Wheeler wrote: > On Apr 8, 2011, at 8:05 AM, Robert Haas wrote: > >>> same mechanism works well in plpgsql and nobody requested a some >>> special shortcut. >> >> I did. That mechanism sucks. But I think we're committed to doing >> what the standard and/or Oracle do, so oh well. > > I think I've worked around that in PL/pgSQL using ALIAS… I've worked around it, too, using various techniques. That doesn't mean it doesn't suck. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Apr 8, 2011, at 8:05 AM, Robert Haas wrote: >> same mechanism works well in plpgsql and nobody requested a some >> special shortcut. > > I did. That mechanism sucks. But I think we're committed to doing > what the standard and/or Oracle do, so oh well. I think I've worked around that in PL/pgSQL using ALIAS… David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Friday, April 08, 2011 04:53:27 PM Pavel Stehule wrote: > same mechanism works well in plpgsql and nobody requested a some > special shortcut. Well, for one it sucks there as well. For another it has been introduced for quite some time and most people have introduced naming like p_param or v_param for parameternames. That has not been the case for sql functions. So I find it way much more painfull there... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Fri, Apr 8, 2011 at 10:53 AM, Pavel Stehule wrote: > same mechanism works well in plpgsql and nobody requested a some > special shortcut. I did. That mechanism sucks. But I think we're committed to doing what the standard and/or Oracle do, so oh well. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On 04/08/2011 10:53 AM, Pavel Stehule wrote: For SQL language functions, I think you're right. The only caveat I have is that if your function name is very long, having to use it as a disambiguating qualifier can be a bit ugly. same mechanism works well in plpgsql and nobody requested a some special shortcut. I get annoyed by it there too, that's why I mentioned it :-) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Hello >> >> Well, if we're going to consider 100% backwards compatibility a "must", >> then we should just stick with what the submitted patch does, ie, >> unqualified names are matched first to query columns, and to parameters >> only if there's no column match. This is also per spec if I interpreted >> Peter's comments correctly. The whole thread started because I >> suggested that throwing an error for ambiguous cases might be a better >> design in the long run, but apparently long term ease of code >> maintenance is far down our list of priorities ... >> >> > > I think the discussion went off into the weeds somewhat, and I'm guilty of > responding to suggestions that don't refer to the original subject. > > For SQL language functions, I think you're right. The only caveat I have is > that if your function name is very long, having to use it as a > disambiguating qualifier can be a bit ugly. same mechanism works well in plpgsql and nobody requested a some special shortcut. Regards Pavel > > cheers > > andrew > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On 04/07/2011 09:58 PM, Tom Lane wrote: Robert Haas writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan wrote: That doesn't mean we should arbitrarily break compatibility with pl/sql, nor that we should feel free to add on warts such as $varname that are completely at odds with the style of the rest of the language. That doesn't do anything except produce a mess. Well, what it does is avoid breaking compatibility with previous versions of PostgreSQL. I think that actually does have some value. Otherwise, we'd be folding to upper-case by default. Well, if we're going to consider 100% backwards compatibility a "must", then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query columns, and to parameters only if there's no column match. This is also per spec if I interpreted Peter's comments correctly. The whole thread started because I suggested that throwing an error for ambiguous cases might be a better design in the long run, but apparently long term ease of code maintenance is far down our list of priorities ... I think the discussion went off into the weeds somewhat, and I'm guilty of responding to suggestions that don't refer to the original subject. For SQL language functions, I think you're right. The only caveat I have is that if your function name is very long, having to use it as a disambiguating qualifier can be a bit ugly. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas wrote: I am halfway tempted to say that we need to invent our own procedural language that is designed not for compatibility with the SQL standard or Oracle, but for non-crappiness. I'm way ahead of you on that one. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Thu, Apr 7, 2011 at 9:58 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan wrote: >>> That doesn't mean we should arbitrarily break compatibility with pl/sql, nor >>> that we should feel free to add on warts such as $varname that are >>> completely at odds with the style of the rest of the language. That doesn't >>> do anything except produce a mess. > >> Well, what it does is avoid breaking compatibility with previous >> versions of PostgreSQL. I think that actually does have some value. >> Otherwise, we'd be folding to upper-case by default. > > Well, if we're going to consider 100% backwards compatibility a "must", > then we should just stick with what the submitted patch does, ie, > unqualified names are matched first to query columns, and to parameters > only if there's no column match. This is also per spec if I interpreted > Peter's comments correctly. The whole thread started because I > suggested that throwing an error for ambiguous cases might be a better > design in the long run, but apparently long term ease of code > maintenance is far down our list of priorities ... Not really. But if you're going to shout down my proposal because it's not in the spec, then it's a bit hard to see how you can argue that we should implement your non-spec-compliant behavior instead, especially at the cost of a painful backward compatibility break. I actually am 100% in agreement with you that allowing ambiguous references to resolve either way is a recipe for bugs, bugs, and more bugs. But breaking people's code is not a better answer. We still have people on 8.2 because the pain of upgrading to 8.3 is more than they can bear, and how many releases have we spent trying to get standard_conforming_strings worked out? I admit this probably wouldn't be as bad, but we've managed to put out several releases in a row now that are relatively painless to upgrade between, and I think that's a trend we should try to keep going. I am halfway tempted to say that we need to invent our own procedural language that is designed not for compatibility with the SQL standard or Oracle, but for non-crappiness. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Apr 7, 2011, at 6:58 PM, Tom Lane wrote: > Well, if we're going to consider 100% backwards compatibility a "must", > then we should just stick with what the submitted patch does, ie, > unqualified names are matched first to query columns, and to parameters > only if there's no column match. This is also per spec if I interpreted > Peter's comments correctly. The whole thread started because I > suggested that throwing an error for ambiguous cases might be a better > design in the long run, but apparently long term ease of code > maintenance is far down our list of priorities ... I agree with you that it should throw an error, at least optionally. Could we not recycle the settings that control this for plpgsql functions? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas writes: > On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan wrote: >> That doesn't mean we should arbitrarily break compatibility with pl/sql, nor >> that we should feel free to add on warts such as $varname that are >> completely at odds with the style of the rest of the language. That doesn't >> do anything except produce a mess. > Well, what it does is avoid breaking compatibility with previous > versions of PostgreSQL. I think that actually does have some value. > Otherwise, we'd be folding to upper-case by default. Well, if we're going to consider 100% backwards compatibility a "must", then we should just stick with what the submitted patch does, ie, unqualified names are matched first to query columns, and to parameters only if there's no column match. This is also per spec if I interpreted Peter's comments correctly. The whole thread started because I suggested that throwing an error for ambiguous cases might be a better design in the long run, but apparently long term ease of code maintenance is far down our list of priorities ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan wrote: > That doesn't mean we should arbitrarily break compatibility with pl/sql, nor > that we should feel free to add on warts such as $varname that are > completely at odds with the style of the rest of the language. That doesn't > do anything except produce a mess. Well, what it does is avoid breaking compatibility with previous versions of PostgreSQL. I think that actually does have some value. Otherwise, we'd be folding to upper-case by default. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On 04/05/2011 03:45 PM, Merlin Moncure wrote: Talking about the standards compliance of functions is a bit silly: our implementation of functions isn't even close to approximating what looks to be the standard (according to this at least: http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html) and there is no point pretending that it is. In practice, database functions and procedures are 100% vendor incompatible with each other, and with the standard. I was just talking about $ getting reserved for some special meaning in the future. mysql supports psm, which we don't. oracle supports pl/sql, which is similar to pl/pgsql, but means nothing in terms of postgresql sql language argument disambiguation afaict. It's our language and we should be able to extend it. That doesn't mean we should arbitrarily break compatibility with pl/sql, nor that we should feel free to add on warts such as $varname that are completely at odds with the style of the rest of the language. That doesn't do anything except produce a mess. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Tue, Apr 5, 2011 at 3:47 PM, Peter Eisentraut wrote: > On tis, 2011-04-05 at 14:45 -0500, Merlin Moncure wrote: >> Talking about the standards compliance of functions is a bit silly: >> our implementation of functions isn't even close to approximating what >> looks to be the standard > > That doesn't mean it couldn't be better in the future. We shouldn't > take it further away, in any case. > > As long as we use LANGUAGE SQL, we are both technically and morally in > standards-space. sql standard functions are psm routines aiui. Are you making the case that 'language sql' in postgresql could or should in fact be psm at some point in the future? I say that's not the case -- our 'language sql' is not psm. That said, if you well and truly stated that it was project objective to allow psm constructions in 'language sql', and you could figure out a way to do that without breaking current sql code, I would have to say i'm coming around to your point of view. Either way, our $N notation is already non-standard and highly in use - what's the big deal about making it more useful? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On tis, 2011-04-05 at 14:45 -0500, Merlin Moncure wrote: > Talking about the standards compliance of functions is a bit silly: > our implementation of functions isn't even close to approximating what > looks to be the standard That doesn't mean it couldn't be better in the future. We shouldn't take it further away, in any case. As long as we use LANGUAGE SQL, we are both technically and morally in standards-space. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/4/5 Merlin Moncure : > On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut wrote: >> On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote: >>> On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut wrote: >>> > On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: >>> >> +1 on using $foo. Even with the standardization risk I think it's the >>> >> best choice. Prefer $"foo" to ${foo} though. >>> > >>> > What standardization risk? The standard has already existed for >10 >>> > years and is widely implemented. >>> >>> What is the standard, and who is it that has implemented it that way? >> >> As mentioned earlier, see under clause on . The >> summary is that in >> >> CREATE FUNCTION foo(a int) >> >> you can refer to the parameter as either of >> >> a >> foo.a >> >> with some scoping rules to resolve ambiguities with column references. >> (These are essentially the same scoping rules that tell you what "a" >> refers to when you have multiple tables with an "a" column in a query.) >> >> As far as I can tell, the syntax is implemented, more or less, at least >> in Oracle, DB2, MySQL, Firebird, and HSQL. I haven't checked what they >> do with the scoping rules, of course. > > Talking about the standards compliance of functions is a bit silly: > our implementation of functions isn't even close to approximating what > looks to be the standard (according to this at least: > http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html) > and there is no point pretending that it is. In practice, database > functions and procedures are 100% vendor incompatible with each other, > and with the standard. I was just talking about $ getting reserved > for some special meaning in the future. > > mysql supports psm, which we don't. A PSM support for PostgreSQL is almost done. I expect a production quality for 9.2. MySQL support own language based on PSM with lot of inspiration in T-SQL. In MySQL - local variables are clasic, only session variables has a prefix @. Regards Pavel oracle supports pl/sql, which is > similar to pl/pgsql, but means nothing in terms of postgresql sql > language argument disambiguation afaict. It's our language and we > should be able to extend it. > > merlin > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/4/5 Peter Eisentraut : > On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote: >> On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut wrote: >> > On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: >> >> +1 on using $foo. Even with the standardization risk I think it's the >> >> best choice. Prefer $"foo" to ${foo} though. >> > >> > What standardization risk? The standard has already existed for >10 >> > years and is widely implemented. >> >> What is the standard, and who is it that has implemented it that way? > > As mentioned earlier, see under clause on . The > summary is that in > > CREATE FUNCTION foo(a int) > > you can refer to the parameter as either of > > a > foo.a > > with some scoping rules to resolve ambiguities with column references. > (These are essentially the same scoping rules that tell you what "a" > refers to when you have multiple tables with an "a" column in a query.) This is a good design. If we disallow a ambiguities, there isn't a space for bugs. And if anybody needs to accent any parameter, then there are still $n notation. There is lot of notation and I don't think so it is necessary to add new one MySQL, MSSQL uses @, DB2, ANSI SQL no prefix, Oracle and Firebird uses ":", but in different context. simply - chaos. There was request for some alias on function name. It could be. PL/pgSQL knows a #option, so there can be some similar in SQL. CREATE OR REPLACE FUNCTION longnamefunc(param integer) RETURNS ... AS $$ #alias longnamefunc ln SELECT ln.param; $$ Regards Pavel Stehule > > As far as I can tell, the syntax is implemented, more or less, at least > in Oracle, DB2, MySQL, Firebird, and HSQL. I haven't checked what they > do with the scoping rules, of course. > > > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut wrote: > On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote: >> On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut wrote: >> > On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: >> >> +1 on using $foo. Even with the standardization risk I think it's the >> >> best choice. Prefer $"foo" to ${foo} though. >> > >> > What standardization risk? The standard has already existed for >10 >> > years and is widely implemented. >> >> What is the standard, and who is it that has implemented it that way? > > As mentioned earlier, see under clause on . The > summary is that in > > CREATE FUNCTION foo(a int) > > you can refer to the parameter as either of > > a > foo.a > > with some scoping rules to resolve ambiguities with column references. > (These are essentially the same scoping rules that tell you what "a" > refers to when you have multiple tables with an "a" column in a query.) > > As far as I can tell, the syntax is implemented, more or less, at least > in Oracle, DB2, MySQL, Firebird, and HSQL. I haven't checked what they > do with the scoping rules, of course. Talking about the standards compliance of functions is a bit silly: our implementation of functions isn't even close to approximating what looks to be the standard (according to this at least: http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html) and there is no point pretending that it is. In practice, database functions and procedures are 100% vendor incompatible with each other, and with the standard. I was just talking about $ getting reserved for some special meaning in the future. mysql supports psm, which we don't. oracle supports pl/sql, which is similar to pl/pgsql, but means nothing in terms of postgresql sql language argument disambiguation afaict. It's our language and we should be able to extend it. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote: > On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut wrote: > > On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: > >> +1 on using $foo. Even with the standardization risk I think it's the > >> best choice. Prefer $"foo" to ${foo} though. > > > > What standardization risk? The standard has already existed for >10 > > years and is widely implemented. > > What is the standard, and who is it that has implemented it that way? As mentioned earlier, see under clause on . The summary is that in CREATE FUNCTION foo(a int) you can refer to the parameter as either of a foo.a with some scoping rules to resolve ambiguities with column references. (These are essentially the same scoping rules that tell you what "a" refers to when you have multiple tables with an "a" column in a query.) As far as I can tell, the syntax is implemented, more or less, at least in Oracle, DB2, MySQL, Firebird, and HSQL. I haven't checked what they do with the scoping rules, of course. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Merlin Moncure wrote: On Fri, Mar 25, 2011 at 10:05 PM, David E. Wheeler wrote: On Mar 25, 2011, at 9:12 PM, Robert Haas wrote: As I've said before, I believe that the root cause of this problem is that using the same syntax for variables and column names is a bad idea in the first place. If we used $foo or ?foo or ${foo} or $.foo or &&foo!!$#? to mean "the parameter called foo", then this would all be a non-issue. Yes *please*. Man that would make maintenance of such functions easier. +1 on using $foo. Even with the standardization risk I think it's the best choice. Prefer $"foo" to ${foo} though. The "foo" syntax should be orthogonal to everything else and not have anything specifically to do with parameters. Rather, "foo" anywhere is just a delimited case-sensitive identifier and can be used anywhere that foo can where the latter is a case-insensitive identifier. As for the SQL standard for bind parameters, as I recall they use :foo and so :"foo" would be the sensitive more general case of that. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut wrote: > On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: >> +1 on using $foo. Even with the standardization risk I think it's the >> best choice. Prefer $"foo" to ${foo} though. > > What standardization risk? The standard has already existed for >10 > years and is widely implemented. What is the standard, and who is it that has implemented it that way? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: > +1 on using $foo. Even with the standardization risk I think it's the > best choice. Prefer $"foo" to ${foo} though. What standardization risk? The standard has already existed for >10 years and is widely implemented. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Fri, Mar 25, 2011 at 10:05 PM, David E. Wheeler wrote: > On Mar 25, 2011, at 9:12 PM, Robert Haas wrote: > >> >> As I've said before, I believe that the root cause of this problem is >> that using the same syntax for variables and column names is a bad >> idea in the first place. If we used $foo or ?foo or ${foo} or $.foo >> or &&foo!!$#? to mean "the parameter called foo", then this would all >> be a non-issue. > > Yes *please*. Man that would make maintenance of such functions easier. +1 on using $foo. Even with the standardization risk I think it's the best choice. Prefer $"foo" to ${foo} though. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Mar 28, 2011, at 3:18 PM, Peter Eisentraut wrote: > On lör, 2011-03-26 at 09:41 -0400, Robert Haas wrote: >> You can't be guaranteed that they won't standardize something >> incompatible no matter what we do. We could choose to do it as you've >> proposed and they could then standardize some weird syntax - the => is >> a fairly relevant example of exactly that. > > The matter of how to resolve SQL parameter names is already > standardized. See clause on . Was there a final consensus on this? FWIW, if we go with using function name, it'd be nice to be allowed to alias that. I don't have a strong opinion between that and using : or $ or whatever. I do feel strongly that we must continue to support existing SQL functions in a reasonable fashion. Having the function blow up on the first invocation is no better than breaking the dump. There should be either a backwards-compatibility mode, or better yet, a way to automatically convert functions to be compatible with the new syntax. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On lör, 2011-03-26 at 09:41 -0400, Robert Haas wrote: > You can't be guaranteed that they won't standardize something > incompatible no matter what we do. We could choose to do it as you've > proposed and they could then standardize some weird syntax - the => is > a fairly relevant example of exactly that. The matter of how to resolve SQL parameter names is already standardized. See clause on . -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/3/27 Martijn van Oosterhout : > On Sun, Mar 27, 2011 at 06:52:03PM +0200, Pavel Stehule wrote: >> Syntax for named parameters should be consistent with prepared >> statement. Is there any comments in standard? > > Well, there's section 4.24 which says: > > "In SQL-statements that are executed dynamically, the parameters are > called dynamic parameters (s) and are > represented in SQL language by a (?)." > ok, so my preferences: 1. no prefix - using a qualified identifiers - it's consistent with plpgsql and sql/psm 2 :name - it uses a Oracle and it can help to people who migrate from Oracle 3. $name - it is like enhancing of $number parameters - lot of languages uses this notation Regards Pavel Stehule > which I think we can all agree is not a useful guide. > > Have a nice day, > > -- > Martijn van Oosterhout http://svana.org/kleptog/ >> Patriotism is when love of your own people comes first; nationalism, >> when hate for people other than your own comes first. >> - Charles de Gaulle > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iD8DBQFNj2+UIB7bNG8LQkwRAjGeAJ99K0Zrp1EM/ike6h+cL5ZEH1Lb2QCfY28L > 7ETpiWfLtD90ISCVMvEgaSs= > =YEx2 > -END PGP SIGNATURE- > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Sun, Mar 27, 2011 at 06:52:03PM +0200, Pavel Stehule wrote: > Syntax for named parameters should be consistent with prepared > statement. Is there any comments in standard? Well, there's section 4.24 which says: "In SQL-statements that are executed dynamically, the parameters are called dynamic parameters (s) and are represented in SQL language by a (?)." which I think we can all agree is not a useful guide. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle signature.asc Description: Digital signature
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/3/27 Andrew Dunstan : > > > On 03/27/2011 09:42 AM, David Fetter wrote: >> >> On Fri, Mar 25, 2011 at 09:12:33PM -0400, Robert Haas wrote: >>> >>> As I've said before, I believe that the root cause of this problem is >>> that using the same syntax for variables and column names is a bad >>> idea in the first place. If we used $foo or ?foo or ${foo} or $.foo >>> or&&foo!!$#? to mean "the parameter called foo", then this would all >>> be a non-issue. >> >> How about psql's :foo syntax? >> >> > > Surely the time has long gone when we could have made such a choice. And the > choice was not made in a vacuum. > Syntax for named parameters should be consistent with prepared statement. Is there any comments in standard? Regards Pavel SQL/PSM doesn't use any prefix - but it is little bit different chapter then prepared statements. > cheers > > andrew > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On 03/27/2011 09:42 AM, David Fetter wrote: On Fri, Mar 25, 2011 at 09:12:33PM -0400, Robert Haas wrote: As I've said before, I believe that the root cause of this problem is that using the same syntax for variables and column names is a bad idea in the first place. If we used $foo or ?foo or ${foo} or $.foo or&&foo!!$#? to mean "the parameter called foo", then this would all be a non-issue. How about psql's :foo syntax? Surely the time has long gone when we could have made such a choice. And the choice was not made in a vacuum. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Fri, Mar 25, 2011 at 09:12:33PM -0400, Robert Haas wrote: > On Fri, Mar 25, 2011 at 8:58 PM, Tom Lane wrote: > > Robert Haas writes: > >> On Mar 25, 2011, at 7:45 PM, Tom Lane wrote: > >>> Well, maybe, but it's not like it's subtle or hard to fix. > > > >> Depends how much of it you have. I've become very skeptical of > >> anything that breaks pg_dump-and-reload-ability. > > > > This wouldn't break pg_dump scripts, because they disable > > check_function_bodies. You would get a failure on first *use* > > of a function, which is something different. > > > > Basically my concern here is that in the name of easing a short-term > > conversion issue, we'll be condemning users to a future of subtle, > > hard-to-find bugs due to ambiguous names. How many hundreds of > > reports have we seen about the equivalent problem in plpgsql? > > > > You could argue that the frequency of plpgsql issues was at least partly > > due to having a poor choice of which way to resolve the ambiguity, but > > I don't think it can be entirely blamed on that. > > As I've said before, I believe that the root cause of this problem is > that using the same syntax for variables and column names is a bad > idea in the first place. If we used $foo or ?foo or ${foo} or $.foo > or &&foo!!$#? to mean "the parameter called foo", then this would all > be a non-issue. How about psql's :foo syntax? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Mar 27, 2011, at 6:11 AM, Dimitri Fontaine wrote: > Robert Haas writes: >> That syntax is sufficiently unwieldly that few people will want to use >> it in real life, but certainly the backward compatibility problem is >> much less than with what Tom proposed. > > Well, we would still support positional arguments like $1 $2 etc, right? Yeah, that's not going away. ...Robert
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas writes: > On Sat, Mar 26, 2011 at 5:19 PM, Dimitri Fontaine > wrote: >> I think the best choice is to only accept qualified parameter names in >> SQL functions (function_name.parameter_name). If a referenced table >> share the function's name, ERROR out and HINT to alias the table name. >> >> If we allow more than that, we're opening the door to ambiguity, bug >> reports, and more than that costly migrations. I don't see any benefit >> in having to audit all SQL functions for ambiguity on a flag day, when >> this could be avoided easily. > > That syntax is sufficiently unwieldly that few people will want to use > it in real life, but certainly the backward compatibility problem is > much less than with what Tom proposed. Well, we would still support positional arguments like $1 $2 etc, right? In Pavel's example I wouldn't mind about using the "values" parameter name but would stick to using $1. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Sat, Mar 26, 2011 at 5:19 PM, Dimitri Fontaine wrote: > I think the best choice is to only accept qualified parameter names in > SQL functions (function_name.parameter_name). If a referenced table > share the function's name, ERROR out and HINT to alias the table name. > > If we allow more than that, we're opening the door to ambiguity, bug > reports, and more than that costly migrations. I don't see any benefit > in having to audit all SQL functions for ambiguity on a flag day, when > this could be avoided easily. That syntax is sufficiently unwieldly that few people will want to use it in real life, but certainly the backward compatibility problem is much less than with what Tom proposed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/3/26 Dimitri Fontaine : > Joshua Berkus writes: >>> Personally I'd vote for *not* having any such dangerous semantics as >>> that. We should have learned better by now from plpgsql experience. >>> I think the best idea is to throw error for ambiguous references, >>> period. >> >> As a likely heavy user of this feature, I agree with Tom here. I really >> don't want the column being silently preferred in SQL functions, when >> PL/pgSQL functions are throwing an error. I'd end up spending hours >> debugging this. > > +1 > > I think the best choice is to only accept qualified parameter names in > SQL functions (function_name.parameter_name). If a referenced table > share the function's name, ERROR out and HINT to alias the table name. it's maybe too hard. I agree so we should to use a function_name alias when collision is possible. Still there are more use cases, where SQL function is used as macro, and there a alias isn't necessary CREATE OR REPLACE FUNCTION greatest(VARIADIC "values" anyarray) RETURNS anyelement AS $$ SELECT max(v) FROM unnest("values") $$ LANGUAGE sql; Regards Pavel > > If we allow more than that, we're opening the door to ambiguity, bug > reports, and more than that costly migrations. I don't see any benefit > in having to audit all SQL functions for ambiguity on a flag day, when > this could be avoided easily. > > Regards, > -- > Dimitri Fontaine > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Joshua Berkus writes: >> Personally I'd vote for *not* having any such dangerous semantics as >> that. We should have learned better by now from plpgsql experience. >> I think the best idea is to throw error for ambiguous references, >> period. > > As a likely heavy user of this feature, I agree with Tom here. I really > don't want the column being silently preferred in SQL functions, when > PL/pgSQL functions are throwing an error. I'd end up spending hours > debugging this. +1 I think the best choice is to only accept qualified parameter names in SQL functions (function_name.parameter_name). If a referenced table share the function's name, ERROR out and HINT to alias the table name. If we allow more than that, we're opening the door to ambiguity, bug reports, and more than that costly migrations. I don't see any benefit in having to audit all SQL functions for ambiguity on a flag day, when this could be avoided easily. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Mar 25, 2011, at 11:23 PM, Tom Lane wrote: > If this were PL/perl, or PL/almost-anything-except-SQL, I could get > behind such a proposal. But it's not, it's SQL; and SQL doesn't do > things that way. SQL's idea of disambiguation is qualified names. > > And even more to the point: to the extent you think that weird syntax > might be a suitable solution, you have to keep in mind that the SQL > committee could take over any such syntax at the drop of a hat. > See the recent unpleasantness concerning => ... You can't be guaranteed that they won't standardize something incompatible no matter what we do. We could choose to do it as you've proposed and they could then standardize some weird syntax - the => is a fairly relevant example of exactly that. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Mar 25, 2011, at 11:23 PM, Tom Lane wrote: > If this were PL/perl, or PL/almost-anything-except-SQL, I could get > behind such a proposal. But it's not, it's SQL; and SQL doesn't do > things that way. SQL's idea of disambiguation is qualified names. > > And even more to the point: to the extent you think that weird syntax > might be a suitable solution, you have to keep in mind that the SQL > committee could take over any such syntax at the drop of a hat. > See the recent unpleasantness concerning => ... Perhaps we could use `=>varname`. ;-P David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas writes: > As I've said before, I believe that the root cause of this problem is > that using the same syntax for variables and column names is a bad > idea in the first place. If we used $foo or ?foo or ${foo} or $.foo > or &&foo!!$#? to mean "the parameter called foo", then this would all > be a non-issue. If this were PL/perl, or PL/almost-anything-except-SQL, I could get behind such a proposal. But it's not, it's SQL; and SQL doesn't do things that way. SQL's idea of disambiguation is qualified names. And even more to the point: to the extent you think that weird syntax might be a suitable solution, you have to keep in mind that the SQL committee could take over any such syntax at the drop of a hat. See the recent unpleasantness concerning => ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Mar 25, 2011, at 9:12 PM, Robert Haas wrote: > > As I've said before, I believe that the root cause of this problem is > that using the same syntax for variables and column names is a bad > idea in the first place. If we used $foo or ?foo or ${foo} or $.foo > or &&foo!!$#? to mean "the parameter called foo", then this would all > be a non-issue. Yes *please*. Man that would make maintenance of such functions easier. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas writes: > On Mar 25, 2011, at 9:22 PM, Joshua Berkus wrote: >> Also, I don't understand why this would be a dump/reload issue if $1 and $2 >> continue to work. > Because an identifier that previously referred unambiguously to a column > might now be ambiguous, if there is a parameter with the same name. Yes, a function that previously worked might now throw error, if we make ambiguous names be errors. But this is *not* a failure that would occur during dump/reload. You'd have to actually run the function. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Mar 25, 2011, at 9:22 PM, Joshua Berkus wrote: > Tom, > >> Personally I'd vote for *not* having any such dangerous semantics as >> that. We should have learned better by now from plpgsql experience. >> I think the best idea is to throw error for ambiguous references, >> period. > > As a likely heavy user of this feature, I agree with Tom here. I really > don't want the column being silently preferred in SQL functions, when > PL/pgSQL functions are throwing an error. I'd end up spending hours > debugging this. > > Also, I don't understand why this would be a dump/reload issue if $1 and $2 > continue to work. > Because an identifier that previously referred unambiguously to a column might now be ambiguous, if there is a parameter with the same name. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Tom, > Personally I'd vote for *not* having any such dangerous semantics as > that. We should have learned better by now from plpgsql experience. > I think the best idea is to throw error for ambiguous references, > period. As a likely heavy user of this feature, I agree with Tom here. I really don't want the column being silently preferred in SQL functions, when PL/pgSQL functions are throwing an error. I'd end up spending hours debugging this. Also, I don't understand why this would be a dump/reload issue if $1 and $2 continue to work. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Fri, Mar 25, 2011 at 8:58 PM, Tom Lane wrote: > Robert Haas writes: >> On Mar 25, 2011, at 7:45 PM, Tom Lane wrote: >>> Well, maybe, but it's not like it's subtle or hard to fix. > >> Depends how much of it you have. I've become very skeptical of >> anything that breaks pg_dump-and-reload-ability. > > This wouldn't break pg_dump scripts, because they disable > check_function_bodies. You would get a failure on first *use* > of a function, which is something different. > > Basically my concern here is that in the name of easing a short-term > conversion issue, we'll be condemning users to a future of subtle, > hard-to-find bugs due to ambiguous names. How many hundreds of > reports have we seen about the equivalent problem in plpgsql? > > You could argue that the frequency of plpgsql issues was at least partly > due to having a poor choice of which way to resolve the ambiguity, but > I don't think it can be entirely blamed on that. As I've said before, I believe that the root cause of this problem is that using the same syntax for variables and column names is a bad idea in the first place. If we used $foo or ?foo or ${foo} or $.foo or &&foo!!$#? to mean "the parameter called foo", then this would all be a non-issue. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas writes: > On Mar 25, 2011, at 7:45 PM, Tom Lane wrote: >> Well, maybe, but it's not like it's subtle or hard to fix. > Depends how much of it you have. I've become very skeptical of > anything that breaks pg_dump-and-reload-ability. This wouldn't break pg_dump scripts, because they disable check_function_bodies. You would get a failure on first *use* of a function, which is something different. Basically my concern here is that in the name of easing a short-term conversion issue, we'll be condemning users to a future of subtle, hard-to-find bugs due to ambiguous names. How many hundreds of reports have we seen about the equivalent problem in plpgsql? You could argue that the frequency of plpgsql issues was at least partly due to having a poor choice of which way to resolve the ambiguity, but I don't think it can be entirely blamed on that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Mar 25, 2011, at 7:45 PM, Tom Lane wrote: > Well, maybe, but it's not like it's subtle or hard to fix. Depends how much of it you have. I've become very skeptical of anything that breaks pg_dump-and-reload-ability. And doubly so now that such problems also mean breaking pg_upgrade after the old cluster has been shut down. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Robert Haas writes: > On Mar 25, 2011, at 4:20 PM, Tom Lane wrote: >> But I don't think that's necessary. Up to now there's been relatively >> little use for naming the parameters of SQL functions, so I think there >> will be few conflicts in the field if we just change the behavior. > Oh wow, I don't agree with that at all. People may name the parameters for > documentation purposes, and then have things like WHERE foo = $1, foo > happening also to be the name associated with $1. Boom! Well, maybe, but it's not like it's subtle or hard to fix. > In any case, I think this is 9.2 material. Oh, of course. It *is* just a WIP patch, anyway. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Mar 25, 2011, at 4:20 PM, Tom Lane wrote: > GUCs are not tremendously helpful for problems such as this. If we > actually wanted to preserve full backwards compatibility, we'd need to > think of a way to mark SQL functions per-function as to what to do. > But I don't think that's necessary. Up to now there's been relatively > little use for naming the parameters of SQL functions, so I think there > will be few conflicts in the field if we just change the behavior. Oh wow, I don't agree with that at all. People may name the parameters for documentation purposes, and then have things like WHERE foo = $1, foo happening also to be the name associated with $1. Boom! In any case, I think this is 9.2 material. We need to get a beta out the door, and I emphatically think we should be focusing on resolving the issues with features already committed, not committing new ones. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/3/25 Tom Lane : > Pavel Stehule writes: >> 2011/3/25 Tom Lane : >>> I think the best idea is to throw error for ambiguous references, >>> period. > >> There can be GUC for controlling use or don't use a parameter names. I >> am for GUC, because there will be a bilion conflicts. But a talk about >> priorities - sql identifier or parameter is useless. > > GUCs are not tremendously helpful for problems such as this. If we > actually wanted to preserve full backwards compatibility, we'd need to > think of a way to mark SQL functions per-function as to what to do. > But I don't think that's necessary. Up to now there's been relatively > little use for naming the parameters of SQL functions, so I think there > will be few conflicts in the field if we just change the behavior. The > mess and complication we have for the comparable behavior in plpgsql > seemed necessary because of the number of existing usages that would > certainly break --- but I doubt that SQL-language functions will have > anywhere near as big a problem. should be nice some converting tool for pg_dump or pg_upgrade. It can dump SQL functions with only qualified identifiers. Pavel > > regards, tom lane > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Pavel Stehule writes: > 2011/3/25 Tom Lane : >> I think the best idea is to throw error for ambiguous references, >> period. > There can be GUC for controlling use or don't use a parameter names. I > am for GUC, because there will be a bilion conflicts. But a talk about > priorities - sql identifier or parameter is useless. GUCs are not tremendously helpful for problems such as this. If we actually wanted to preserve full backwards compatibility, we'd need to think of a way to mark SQL functions per-function as to what to do. But I don't think that's necessary. Up to now there's been relatively little use for naming the parameters of SQL functions, so I think there will be few conflicts in the field if we just change the behavior. The mess and complication we have for the comparable behavior in plpgsql seemed necessary because of the number of existing usages that would certainly break --- but I doubt that SQL-language functions will have anywhere near as big a problem. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/3/25 Tom Lane : > Matthew Draper writes: >> Attached is a WIP patch that allows SQL-language functions to reference >> their parameters by name. > >> It uses p_post_columnref_hook, so potentially ambiguous references >> prefer the column... that seems to make the most sense, both because it >> avoids a backwards incompatibility, and it conforms with SQL's usual >> notion of assuming you mean the "nearest" name. > > Personally I'd vote for *not* having any such dangerous semantics as > that. We should have learned better by now from plpgsql experience. > I think the best idea is to throw error for ambiguous references, > period. That means you do need ways to disambiguate in both directions. > For column references you can just qualify with the table name/alias. > If the parameter reference is intended, allow qualification with the > function name. I agree with Tom. There can be GUC for controlling use or don't use a parameter names. I am for GUC, because there will be a bilion conflicts. But a talk about priorities - sql identifier or parameter is useless. Regards Pavel Stehule > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Matthew Draper writes: > Attached is a WIP patch that allows SQL-language functions to reference > their parameters by name. > It uses p_post_columnref_hook, so potentially ambiguous references > prefer the column... that seems to make the most sense, both because it > avoids a backwards incompatibility, and it conforms with SQL's usual > notion of assuming you mean the "nearest" name. Personally I'd vote for *not* having any such dangerous semantics as that. We should have learned better by now from plpgsql experience. I think the best idea is to throw error for ambiguous references, period. That means you do need ways to disambiguate in both directions. For column references you can just qualify with the table name/alias. If the parameter reference is intended, allow qualification with the function name. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Attached is a WIP patch that allows SQL-language functions to reference their parameters by name. It uses p_post_columnref_hook, so potentially ambiguous references prefer the column... that seems to make the most sense, both because it avoids a backwards incompatibility, and it conforms with SQL's usual notion of assuming you mean the "nearest" name. It allows the parameter name to be qualified with the function name, for when you really mean you want the parameter. This being my first foray into the PostgreSQL source, I expect the code is horribly wrong in a variety of ways. Other than that, the regression tests I've been using are a slight modification of existing queries; I imagine they should look measurably different. And finally, conspicuously absent are the documentation changes that will obviously need to accompany a real patch. (This builds & passes `make check` on current HEAD, a4425e3) Thanks! Matthew -- matt...@trebex.net diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c new file mode 100644 index ce3b77b..be71fbb *** a/src/backend/executor/functions.c --- b/src/backend/executor/functions.c *** typedef SQLFunctionCache *SQLFunctionCac *** 116,122 --- 116,124 */ typedef struct SQLFunctionParseInfo { + char *name; /* function's name */ Oid *argtypes; /* resolved types of input arguments */ + char **argnames; /* names of input arguments */ int nargs; /* number of input arguments */ Oid collation; /* function's input collation, if known */ } SQLFunctionParseInfo; *** typedef struct SQLFunctionParseInfo *** 124,129 --- 126,133 /* non-export function prototypes */ static Node *sql_fn_param_ref(ParseState *pstate, ParamRef *pref); + static Node *sql_fn_post_column_ref(ParseState *pstate, ColumnRef *cref, Node *var); + static Node *sql_fn_param_ref_num(ParseState *pstate, SQLFunctionParseInfoPtr pinfo, int paramno, int location); static List *init_execution_state(List *queryTree_list, SQLFunctionCachePtr fcache, bool lazyEvalOK); *** prepare_sql_fn_parse_info(HeapTuple proc *** 163,168 --- 167,173 int nargs; pinfo = (SQLFunctionParseInfoPtr) palloc0(sizeof(SQLFunctionParseInfo)); + pinfo->name = NameStr(procedureStruct->proname); /* Save the function's input collation */ pinfo->collation = inputCollation; *** prepare_sql_fn_parse_info(HeapTuple proc *** 201,206 --- 206,241 pinfo->argtypes = argOidVect; } + if (nargs > 0) + { + Datum proargnames; + Datum proargmodes; + int argnum; + int n_arg_names; + bool isNull; + + proargnames = SysCacheGetAttr(PROCNAMEARGSNSP, procedureTuple, + Anum_pg_proc_proargnames, + &isNull); + if (isNull) + proargmodes = PointerGetDatum(NULL); /* just to be sure */ + + proargmodes = SysCacheGetAttr(PROCNAMEARGSNSP, procedureTuple, + Anum_pg_proc_proargmodes, + &isNull); + if (isNull) + proargmodes = PointerGetDatum(NULL); /* just to be sure */ + + n_arg_names = get_func_input_arg_names(proargnames, proargmodes, &pinfo->argnames); + + if (n_arg_names < nargs) + pinfo->argnames = NULL; + } + else + { + pinfo->argnames = NULL; + } + return pinfo; } *** prepare_sql_fn_parse_info(HeapTuple proc *** 210,223 void sql_fn_parser_setup(struct ParseState *pstate, SQLFunctionParseInfoPtr pinfo) { - /* Later we might use these hooks to support parameter names */ pstate->p_pre_columnref_hook = NULL; ! pstate->p_post_columnref_hook = NULL; pstate->p_paramref_hook = sql_fn_param_ref; /* no need to use p_coerce_param_hook */ pstate->p_ref_hook_state = (void *) pinfo; } /* * sql_fn_param_ref parser callback for ParamRefs ($n symbols) */ --- 245,354 void sql_fn_parser_setup(struct ParseState *pstate, SQLFunctionParseInfoPtr pinfo) { pstate->p_pre_columnref_hook = NULL; ! pstate->p_post_columnref_hook = sql_fn_post_column_ref; pstate->p_paramref_hook = sql_fn_param_ref; /* no need to use p_coerce_param_hook */ pstate->p_ref_hook_state = (void *) pinfo; } + static Node * + sql_fn_resolve_name(ParseState *pstate, SQLFunctionParseInfoPtr pinfo, const char *paramname, int location) + { + int i; + for (i = 0; i < pinfo->nargs; i++) + if (pinfo->argnames[i] && strcmp(pinfo->argnames[i], paramname) == 0) + return sql_fn_param_ref_num(pstate, pinfo, i + 1, location); + + return NULL; + } + + /* + * sql_fn_post_column_ref parser callback for ColumnRefs + */ + static Node * + sql_fn_post_column_ref(ParseState *pstate, ColumnRef *cref, Node *var) + { + SQLFunctionParseInfoPtr pinfo = (SQLFunctionParseInfoPtr) pstate->p_ref_hook_state; + int names; + Node *field1; + Node *subfield = NULL; + const char *pname; + Node *param; + + if (var != NULL) + return NULL; /* the