3

I was running into the issue defined in the following article, and the first answer solved my main concern of naming a parameter the same name as a table column. My new concern is that my function/procedure parameters are widely used and the name of my functions/procedures are fairly detailed.

PL/pgSQL column name the same as variable

Is there a way to define an alias for a function or procedure name - to be used inside its body?

Current code:

CREATE OR REPLACE PROCEDURE dbo.PR_DeleteCrazyNamedItemByCrazyNamedID(in NamedID UUID)
  LANGUAGE plpgsql AS
$BODY$
DECLARE
BEGIN
    Delete from dbo.Table t where PR_DeleteCrazyNamedItemByCrazyNamedID.NamedID = t.NamedID;
...

Desired code:

CREATE OR REPLACE PROCEDURE dbo.PR_DeleteCrazyNamedItemByCrazyNamedID(in NamedID UUID) as proc
  LANGUAGE plpgsql AS
$BODY$
DECLARE
BEGIN
    Delete from dbo.Table t where proc.NamedID = t.NamedID;
...
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
theB3RV
  • 894
  • 4
  • 13
  • 33
  • 1
    It seems to be answered literally in [the answer that you linked to](https://stackoverflow.com/questions/29600500/pl-pgsql-column-name-the-same-as-variable/29604751#29604751)? "*It's best to avoid such ambiguities to begin with, that's less error prone. If you need a column name as function parameter name, too, one way to avoid naming collisions would be to use an ALIAS inside the function. That's one of the rare cases where ALIAS is actually useful. Or you can reference input parameter by ordinal position*" – Bergi Jan 29 '20 at 20:09
  • 1
    Why don't you just give the parameter a different name? e.g.: `(p_named_id uuid)` –  Jan 29 '20 at 20:20
  • @bergi I am using an alias on the table name and that's precisely what Im trying to do on the function name. I understand the best practice, I'm just trying to ask if such a thing exists. – theB3RV Jan 30 '20 at 00:12
  • 1
    There is no corresponding __ALIAS__ for a function name as the alias given to a table. As @a_horse_with_no_name said just give your parameter a different name. If you don't like the "p_xxxx" convention then use something like in_name_id or name_id_in, or whatever else suits you. – Belayer Jan 30 '20 at 00:30
  • @Bergi: One tricky detail remains: the function name serves as record holding input parameters, but is itself not accessible to the `ALIAS` feature. – Erwin Brandstetter Jan 30 '20 at 00:57

1 Answers1

5

Not directly. The function name seems to be visible as record containing input parameters inside the function body, but it is not accessible for an ALIAS as suggested in my referenced answer because it actually serves as outer label. The manual:

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 42.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.

But you can combine an ALIAS for function parameters with an outer block label (one nesting level below the built-in outer block labeled with the function name) like this:

General example with a function:

CREATE OR REPLACE FUNCTION weird_procedure_name(named_id int)
  RETURNS TABLE (referenced_how text, input_value int) LANGUAGE plpgsql AS
$func$
<< proc >>  -- outer label!
DECLARE
   named_id ALIAS FOR named_id; -- sic!
BEGIN
   RETURN QUERY VALUES
     ('weird_procedure_name.named_id', weird_procedure_name.named_id)
   , ('proc.named_id', proc.named_id)
   , ('named_id', named_id)
   ;
END
$func$;

SELECT * FROM  weird_procedure_name(666);

referenced_how                | input_value
:---------------------------- | ----------:
weird_procedure_name.named_id |         666
proc.named_id                 |         666
named_id                      |         666

db<>fiddle here

named_id ALIAS FOR named_id; seems to be pointless noise, but now the input parameter is accessible via block label - effectively doing what you ask for. (You might chose a different name while being at it.)
And I would certainly add a code comment explaining why label and alias are needed, lest the next smart developer should be tempted to remove either.

Applied to your example:

CREATE OR REPLACE PROCEDURE PR_DeleteCrazyNamedItemByCrazyNamedID(in NamedID UUID)
  LANGUAGE plpgsql AS
$BODY$
<< proc >>  -- !
DECLARE
   NamedID ALIAS FOR NamedID; -- sic!
BEGIN
   DELETE FROM dbo.tbl t WHERE t.NamedID = proc.NamedID;  -- what you wanted !
END
$BODY$;

I would still much rather work with unique parameter names to begin with, so no qualification is required at all. I like to prefix all parameter names with underscore (like: _named_id) - and never do the same for other object names.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228