Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
(oops this mail never reached out, it seems, resending) Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us 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 Thu, Apr 14, 2011 at 10:56 PM, Pavel Stehule pavel.steh...@gmail.com wrote: 2011/4/15 Jim Nasby j...@nasby.net: On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com 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
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 alvhe...@commandprompt.com 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 Mon, Apr 18, 2011 at 11:33 AM, Alvaro Herrera alvhe...@commandprompt.com 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
Robert Haas robertmh...@gmail.com 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
On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com 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 robertmh...@gmail.com writes: On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine dimi...@2ndquadrant.fr 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 1:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine dimi...@2ndquadrant.fr 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 robertmh...@gmail.com writes: On Thu, Apr 14, 2011 at 1:55 PM, Tom Lane t...@sss.pgh.pa.us 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
Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com 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
On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com 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
2011/4/15 Jim Nasby j...@nasby.net: On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com 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 04/07/2011 09:58 PM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstanand...@dunslane.net 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
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/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
On Fri, Apr 8, 2011 at 10:53 AM, Pavel Stehule pavel.steh...@gmail.com 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 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 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 Fri, Apr 8, 2011 at 11:51 AM, David E. Wheeler da...@kineticode.com 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 Thu, Apr 7, 2011 at 8:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan and...@dunslane.net 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
Merlin Moncure mmonc...@gmail.com writes: On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane t...@sss.pgh.pa.us 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
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
On Fri, Apr 8, 2011 at 3:56 PM, Josh Berkus j...@agliodbs.com 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
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 4:32 PM, Josh Berkus j...@agliodbs.com 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
Robert Haas robertmh...@gmail.com writes: On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus j...@agliodbs.com 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
2011/4/9 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus j...@agliodbs.com 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 robertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan and...@dunslane.net 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 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
On Thu, Apr 7, 2011 at 9:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan and...@dunslane.net 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
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 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 identifier chain. 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 Fri, Mar 25, 2011 at 10:05 PM, David E. Wheeler da...@kineticode.com 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 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 Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut pete...@gmx.net 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
Merlin Moncure wrote:
On Fri, Mar 25, 2011 at 10:05 PM, David E. Wheeler da...@kineticode.com 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 tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote:
On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut pete...@gmx.net 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 identifier chain. 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
On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut pete...@gmx.net wrote:
On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote:
On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut pete...@gmx.net 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 identifier chain. 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
2011/4/5 Peter Eisentraut pete...@gmx.net:
On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote:
On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut pete...@gmx.net 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 identifier chain. 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
2011/4/5 Merlin Moncure mmonc...@gmail.com:
On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut pete...@gmx.net wrote:
On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote:
On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut pete...@gmx.net 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 identifier chain. 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
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
On Tue, Apr 5, 2011 at 3:47 PM, Peter Eisentraut pete...@gmx.net 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 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 5:52 PM, Andrew Dunstan and...@dunslane.net 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 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 identifier chain. -- 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 robertmh...@gmail.com writes: On Sat, Mar 26, 2011 at 5:19 PM, Dimitri Fontaine dimi...@2ndquadrant.fr 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 Mar 27, 2011, at 6:11 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com 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
On Fri, Mar 25, 2011 at 09:12:33PM -0400, Robert Haas wrote:
On Fri, Mar 25, 2011 at 8:58 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Mar 25, 2011, at 7:45 PM, Tom Lane t...@sss.pgh.pa.us 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 da...@fetter.org 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 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
orfoo!!$#? 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
2011/3/27 Andrew Dunstan and...@dunslane.net:
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
orfoo!!$#? 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 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 (dynamic parameter specifications) and are represented in SQL language by a question mark (?). which I think we can all agree is not a useful guide. Have a nice day, -- Martijn van Oosterhout klep...@svana.org 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 Martijn van Oosterhout klep...@svana.org: 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 (dynamic parameter specifications) and are represented in SQL language by a question mark (?). 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 klep...@svana.org 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 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
On Mar 25, 2011, at 11:23 PM, Tom Lane t...@sss.pgh.pa.us 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
Joshua Berkus j...@agliodbs.com 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
2011/3/26 Dimitri Fontaine dimi...@2ndquadrant.fr: Joshua Berkus j...@agliodbs.com 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
On Sat, Mar 26, 2011 at 5:19 PM, Dimitri Fontaine dimi...@2ndquadrant.fr 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
Matthew Draper matt...@trebex.net 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
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/3/25 Tom Lane t...@sss.pgh.pa.us: Matthew Draper matt...@trebex.net 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
Pavel Stehule pavel.steh...@gmail.com writes: 2011/3/25 Tom Lane t...@sss.pgh.pa.us: 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 t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2011/3/25 Tom Lane t...@sss.pgh.pa.us: 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
On Mar 25, 2011, at 4:20 PM, Tom Lane t...@sss.pgh.pa.us 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
Robert Haas robertmh...@gmail.com writes: On Mar 25, 2011, at 4:20 PM, Tom Lane t...@sss.pgh.pa.us 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 7:45 PM, Tom Lane t...@sss.pgh.pa.us 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 robertmh...@gmail.com writes: On Mar 25, 2011, at 7:45 PM, Tom Lane t...@sss.pgh.pa.us 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 Fri, Mar 25, 2011 at 8:58 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Mar 25, 2011, at 7:45 PM, Tom Lane t...@sss.pgh.pa.us 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
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 Mar 25, 2011, at 9:22 PM, Joshua Berkus j...@agliodbs.com 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
Robert Haas robertmh...@gmail.com writes: On Mar 25, 2011, at 9:22 PM, Joshua Berkus j...@agliodbs.com 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: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 robertmh...@gmail.com 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
