Обсуждение: function_name.parameter_name
Howdy, Anyone ever thought to try to add $subject to PL/pgSQL? Someone left a [comment][] on the PGXN blog about how this is a supportedsyntax for using named parameters on Oracle. The context is to avoid conflicts between variable names and columnnames by function-qualifyin the former and table-qualifying the latter. [comment]: http://blog.pgxn.org/post/1053165383/alias-in-vogue#dsq-comment-75687336 Would this be do-able in PL/pgSQL? Best, David
"David E. Wheeler" <david.wheeler@pgexperts.com> writes: > Anyone ever thought to try to add $subject to PL/pgSQL? How does $subject differ from what we already do? See http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html particularly this: Note: There is actually a hidden "outer block" surrounding thebody of any PL/pgSQL function. This block provides thedeclarationsof the function's parameters (if any), as well assome special variables such as FOUND (see Section 39.5.5).Theouter block is labeled with the function's name, meaning thatparameters and special variables can be qualifiedwith thefunction's name. regards, tom lane
On Sep 7, 2010, at 9:35 AM, Tom Lane wrote: > How does $subject differ from what we already do? See > http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html > particularly this: > > Note: There is actually a hidden "outer block" surrounding the > body of any PL/pgSQL function. This block provides the > declarations of the function's parameters (if any), as well as > some special variables such as FOUND (see Section 39.5.5). The > outer block is labeled with the function's name, meaning that > parameters and special variables can be qualified with the > function's name. Well I'll be damned. I never knew about this! So I can get rid of those aliases! http://github.com/theory/pgxn-manager/commit/e5add190ff5358a0b2ede64b62616491be454c50 Thanks Tom, I had *no idea* about this. Best, David
Hi, On 7 September 2010 20:35, Tom Lane <tgl@sss.pgh.pa.us> wrote: > How does $subject differ from what we already do? See > http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html So will it be possible to do things like this? 1. CREATE FUNCTION func_name(arg_name text) RETURNS integer AS $$ BEGIN RAISE INFO '%', func_name.arg_name; ... 2. CREATE FUNCTION func_name() RETURNS integer AS $$ DECLARE var_name text := 'bla'; BEGIN RAISE INFO '%', func_name.var_name; ... 3. CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$ << func_alias >> DECLARE var_name text := 'bla'; BEGIN RAISE INFO '%', func_alias.var_name; ... -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802
I think so. Try it! David On Sep 7, 2010, at 11:39 AM, Sergey Konoplev wrote: > Hi, > > On 7 September 2010 20:35, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> How does $subject differ from what we already do? See >> http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html > > So will it be possible to do things like this? > > 1. > CREATE FUNCTION func_name(arg_name text) RETURNS integer AS $$ > BEGIN > RAISE INFO '%', func_name.arg_name; > ... > > 2. > CREATE FUNCTION func_name() RETURNS integer AS $$ > DECLARE > var_name text := 'bla'; > BEGIN > RAISE INFO '%', func_name.var_name; > ... > > 3. > CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$ > << func_alias >> > DECLARE > var_name text := 'bla'; > BEGIN > RAISE INFO '%', func_alias.var_name;
Sergey Konoplev wrote: > Hi, > > On 7 September 2010 20:35, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > How does $subject differ from what we already do? ?See > > http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html > > So will it be possible to do things like this? > > 1. > CREATE FUNCTION func_name(arg_name text) RETURNS integer AS $$ > BEGIN > RAISE INFO '%', func_name.arg_name; > ... > > 2. > CREATE FUNCTION func_name() RETURNS integer AS $$ > DECLARE > var_name text := 'bla'; > BEGIN > RAISE INFO '%', func_name.var_name; > ... > > 3. > CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$ > << func_alias >> > DECLARE > var_name text := 'bla'; > BEGIN > RAISE INFO '%', func_alias.var_name; > ... In my testing #1 works, but #2 does not: -- #1test=> CREATE OR REPLACE FUNCTION xxx(yyy INTEGER) RETURNS void AS $$BEGIN xxx.yyy := 4;END;$$LANGUAGE plpgsql;CREATEFUNCTION-- #2test=> CREATE OR REPLACE FUNCTION xxx() RETURNS void AS $$DECLARE yyy integer;BEGINxxx.yyy :=4;END;$$LANGUAGE plpgsql;ERROR: "xxx.yyy" is not a known variableLINE 3: xxx.yyy := 4; ^ #2 works only if you specify a label above the DECLARE section and use that label (not the function name) as a variable qualifier: test=> CREATE OR REPLACE FUNCTION xxx() RETURNS void AS $$<<zzz>>DECLARE yyy INTEGER;BEGIN zzz.yyy := 4;END;$$LANGUAGEplpgsql;CREATE FUNCTION Interestingly, I can use a label that matches the function name: test=> CREATE OR REPLACE FUNCTION xxx() RETURNS void AS $$<<xxx>>DECLARE yyy INTEGER;BEGIN xxx.yyy := 4;END;$$LANGUAGEplpgsql;CREATE FUNCTION but if you supply parameters to the function, it does not work: test=> CREATE OR REPLACE FUNCTION xxx(aaa INTEGER) RETURNS void AS $$<<xxx>>DECLARE yyy INTEGER;BEGIN xxx.yyy := 4;END;$$LANGUAGEplpgsql;ERROR: cannot change name of input parameter "yyy"HINT: Use DROP FUNCTION first. so this is not something we can recommend to users. Note the text Tom quoted from our docs: http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html There is actually a hidden <quote>outer block</> surrounding the body of any <application>PL/pgSQL</> function. This block provides the declarations of the function's parameters (if any), as well as some special variablessuch as <literal>FOUND</literal> (see <xref linkend="plpgsql-statements-diagnostics">). The outer blockis labeled with the function's name, meaning that parameters and special variables can be qualified with the function's name. This talks about the parameters, but not about the DECLARE block. The idea of adding a label to DECLARE blocks is mentioned in our docs: http://www.postgresql.org/docs/9.0/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST Alternatively you can qualify ambiguous references to make them clear.In the above example, src.foo would be an unambiguousreference to thetable column. To create an unambiguous reference to a variable, declareit in a labeled block anduse the block's label (see Section 39.2). -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> ... but if you supply parameters to the function, it does not work:
> test=> CREATE OR REPLACE FUNCTION xxx(aaa INTEGER) RETURNS void AS $$
> ERROR: cannot change name of input parameter "yyy"
> HINT: Use DROP FUNCTION first.
This is failing because you tried to redeclare xxx(int) with a different
name for its parameter, which is no longer allowed. It has nothing to
do with the question at hand.
regards, tom lane
Bruce Momjian wrote: > Sergey Konoplev wrote: >> 1. >> CREATE FUNCTION func_name(arg_name text) RETURNS integer AS $$ >> BEGIN >> RAISE INFO '%', func_name.arg_name; >> ... >> >> 2. >> CREATE FUNCTION func_name() RETURNS integer AS $$ >> DECLARE >> var_name text := 'bla'; >> BEGIN >> RAISE INFO '%', func_name.var_name; >> ... >> >> 3. >> CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$ >> << func_alias >> >> DECLARE >> var_name text := 'bla'; >> BEGIN >> RAISE INFO '%', func_alias.var_name; >> ... I suggest that it might be reasonable to introduce a new syntax, that isn't already valid for something inside a routine, and use that as a terse way to reference the current function and/or its parameters. This may best be a simple constant syntax. For example, iff it isn't already valid for a qualified name to have a leading period/full-stop/radix-marker, then this could be introduced as a valid way to refer to the current routine. Then in the above examples you can say: RAISE INFO '%', .arg_name; RAISE INFO '%', .var_name; ... without explicitly declaring a func_alias. In a tangent, you can also use a new constant syntax (unless you have one?) to allow a routine to invoke itself without knowing its own name, which could be nice in a simple recursive routine. Maybe ".(arg,arg)" would do it? I would think this should be non-intrusive and useful and could go in 9.1. -- Darren Duncan
On Sep 8, 2010, at 3:17 PM, Darren Duncan <darren@darrenduncan.net> wrote: > Bruce Momjian wrote: >> Sergey Konoplev wrote: >>> 1. >>> CREATE FUNCTION func_name(arg_name text) RETURNS integer AS $$ >>> BEGIN >>> RAISE INFO '%', func_name.arg_name; >>> ... >>> >>> 2. >>> CREATE FUNCTION func_name() RETURNS integer AS $$ >>> DECLARE >>> var_name text := 'bla'; >>> BEGIN >>> RAISE INFO '%', func_name.var_name; >>> ... >>> >>> 3. >>> CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$ >>> << func_alias >> >>> DECLARE >>> var_name text := 'bla'; >>> BEGIN >>> RAISE INFO '%', func_alias.var_name; >>> ... > > I suggest that it might be reasonable to introduce a new syntax, that isn't already valid for something inside a routine,and use that as a terse way to reference the current function and/or its parameters. This may best be a simple constantsyntax. This has been proposed in the past and Tom has rejected it, but I agree that it would be useful. The key word in this proposalis "terse". ...Robert
Robert Haas wrote: > On Sep 8, 2010, at 3:17 PM, Darren Duncan <darren@darrenduncan.net> wrote: >> Bruce Momjian wrote: >>> Sergey Konoplev wrote: >>>> 3. >>>> CREATE FUNCTION func_very_very_very_very_long_name() RETURNS integer AS $$ >>>> << func_alias >> >>>> DECLARE >>>> var_name text := 'bla'; >>>> BEGIN >>>> RAISE INFO '%', func_alias.var_name; >>>> ... >> I suggest that it might be reasonable to introduce a new syntax, that isn't already valid for something inside a routine,and use that as a terse way to reference the current function and/or its parameters. This may best be a simple constantsyntax. > > This has been proposed in the past and Tom has rejected it, but I agree that it would be useful. The key word in thisproposal is "terse". Absolutely. In fact I'm not particularly enamored with my ".foo" example suggestion because I would actually prefer for that particular syntax to be left unused and available for other possible future uses that are better thought out. I think instead that something akin to an explicit alias would both be more future-proofed and be the least surprising to existing users, as per #3. If the alias was very short, then we have something terse for usage. I should also say that this subject has some bearing on the topic of aliases or synonyms in general. In the situations where one wants an entity to be referenceable by more than one name, and knows this at the time of declaring said entity, there could be a syntax for declaring the extra names inline with the original. For example, if it wouldn't conflict with anything, one could use the "|" symbol (mnemonic is that means "alternation" in regular expressions) like this: CREATE FUNCTION func_very_very_very_very_long_name|short_name() ... ... but this could use some work since I also see that being useful for declaring synonyms inline, which are public names like the original, not just internal private names. When used for synonyms, this would still be represented in the system catalog as a function named func_very... and a synonym named short_name, this synonym being akin to a Unix soft link or a C symbolic alias in semantics. Similarly, and mainly for use with named argument syntax, a named parameter could have several names it could go by, declared with | also. Example: CREATE FUNCTION func_name(arg_name|altnm text) ... It doesn't have to be that syntax, but I demonstrated a principle, and I personally like "|" for the mnemonic. -- Darren Duncan
Excerpts from Darren Duncan's message of mié sep 08 17:41:40 -0400 2010: > For example, if it wouldn't conflict with anything, one could use the "|" symbol > (mnemonic is that means "alternation" in regular expressions) like this: > > CREATE FUNCTION func_very_very_very_very_long_name|short_name() ... If you can name the function short_name, why not use just that in the first place? -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Excerpts from Darren Duncan's message of mié sep 08 17:41:40 -0400 2010: > >> For example, if it wouldn't conflict with anything, one could use the "|" symbol >> (mnemonic is that means "alternation" in regular expressions) like this: >> >> CREATE FUNCTION func_very_very_very_very_long_name|short_name() ... > > If you can name the function short_name, why not use just that in the > first place? More realistic examples would be either of: 1. Offer users the choice of a longer more self-describing name and a terser name. For example: "function is_member_of|in (...) ...". 2. Offer users the choice of similar length but different names. For example: "function sum|add(x integer, y integer) returns integer ...". 3. Make it easier to change your mind on a name while providing backwards compatibility for awhile. For example: "function new_name|old_name (...) ...". Personally I like the idea of developers not always having to be forced to choose among two equally good names, and making a wrapper function would be overkill for this feature. -- Darren Duncan
Excerpts from Darren Duncan's message of mié sep 08 18:29:35 -0400 2010: > Personally I like the idea of developers not always having to be forced to > choose among two equally good names, and making a wrapper function would be > overkill for this feature. While I don't agree with the idea of providing extra names that are probably mostly going to increase the confusion of someone trying to understand such a system, I think this use case would be well covered by synonyms. But these would be defined by a new SQL command, say CREATE SYNONYM, not by funny notation on the initial CREATE FUNCTION call. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Excerpts from Darren Duncan's message of mié sep 08 18:29:35 -0400 2010: > >> Personally I like the idea of developers not always having to be forced to >> choose among two equally good names, and making a wrapper function would be >> overkill for this feature. > > While I don't agree with the idea of providing extra names that are > probably mostly going to increase the confusion of someone trying to > understand such a system, I think this use case would be well covered by > synonyms. But these would be defined by a new SQL command, say CREATE > SYNONYM, not by funny notation on the initial CREATE FUNCTION call. Yes, and having a more general solution like CREATE SYNONYM is more important to have anyway. My "|" is simply a syntactic shorthand for a special case of CREATE SYNONYM, with respect to schema objects, and would parse into the same thing. I don't feel any need now for me to push this shorthand further. -- Darren Duncan
On Sep 8, 2010, at 3:57 PM, Darren Duncan wrote: >> While I don't agree with the idea of providing extra names that are >> probably mostly going to increase the confusion of someone trying to >> understand such a system, I think this use case would be well covered by >> synonyms. But these would be defined by a new SQL command, say CREATE >> SYNONYM, not by funny notation on the initial CREATE FUNCTION call. Sounds handy. > Yes, and having a more general solution like CREATE SYNONYM is more important to have anyway. My "|" is simply a syntacticshorthand for a special case of CREATE SYNONYM, with respect to schema objects, and would parse into the same thing. I don't feel any need now for me to push this shorthand further. -- Darren Duncan I can't get excited about it. I'm just happy the functionality is there. I was able to both simplify my PL/pgSQL code *and*make it much clearer what it's doing: http://github.com/theory/pgxn-manager/commit/e136ccb342010e836c39dafa43b802478be445a0 That said, I'm assuming that the function-name block is really a RECORD object representing the argument signature. I couldsee a case for PL/pgSQL just having an "ARGS" variable or something that does the same thing. Kind of like triggershave NEW and OLD. But given that the functionality is already there, that's just gravy. Or sugar. I'm not sure which.Sugary gravy. Best, David