CODEWITHSUNDEEP
postgresqlstored-proceduresaliasplpgsql
theB3RV
theB3RV

Reputation: 924

Alias for function or procedure name

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

Upvotes: 4

Views: 1912

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658202

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.

Upvotes: 6

Related Questions