Reference a parameter in Postgres function

Question

The table org has a column called npi. Why does query1 work and query2 not?

Query 1 -

CREATE OR REPLACE FUNCTION check (npi TEXT)
RETURNS BOOLEAN AS $$

DECLARE
   pass_npi TEXT;

BEGIN
   pass_npi := npi;

   SELECT 1
   FROM org doc
   WHERE doc.npi = pass_npi
   ;

   RETURN 1;

END $$

Query 2 -

CREATE OR REPLACE FUNCTION check (npi TEXT)
RETURNS BOOLEAN AS $$

BEGIN

   SELECT 1
   FROM org doc
   WHERE doc.npi = npi
   ;

   RETURN 1;

END $$

ERROR -

Ambigious column name NPI

Laurenz Albe · Accepted Answer · 2020-04-24 13:11:22Z

9

Because in the second case it is unclear if npi is the table column (that would be a valid, if useless statement) or the function parameter.

There are three solutions apart from the one in your first query:

The best one: use function parameters that have names different from table columns. This can be done by using a prefix:
```
CREATE FUNCTION check (p_npi TEXT) RETURNS boolean AS
   ...
   SELECT ...
   WHERE doc.npi = p_npi
```

Use the ALIAS command to “rename” the parameter:

CREATE FUNCTION check (npi TEXT) RETURNS boolean AS
$$DECLARE
   p_npi ALIAS FOR npi;
BEGIN
   ...
   SELECT ...
   WHERE doc.npi = p_npi

Qualify the parameter with the function name:

CREATE FUNCTION check (npi TEXT) RETURNS boolean AS
   ...
   SELECT ...
   WHERE doc.npi = check.npi

answered Apr 24, 2020 at 13:11

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jbeltran · Accepted Answer · 2020-04-24 13:06:48Z

0

What happens is that in Query2 you are comparing the field the doc.npi field with it, it is the same to say doc.npi and to say npi, for that reason it shows you that the sentence is ambiguous, on the contrary case in Query1 you are comparing the doc.npi field with a different field that is the pass_npi.

To solve this problem you must compare the same columns but from different tables or different columns from the same table.

Query2:

    CREATE OR REPLACE FUNCTION check (npi TEXT)
            RETURNS BOOLEAN AS $$

            BEGIN

               SELECT 1
               FROM org doc
               WHERE doc.npi = pass_npi
               ;

               RETURN 1;

            END $$

answered Apr 24, 2020 at 13:06

Jbeltran

1115 bronze badges

Collectives™ on Stack Overflow

Reference a parameter in Postgres function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related