CODEWITHSUNDEEP
sqlpostgresql
Aaron
Aaron

Reputation: 1455

Reference a parameter in Postgres function

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

Upvotes: 5

Views: 3726

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246163

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:

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

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

  3. Qualify the parameter with the function name:

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

Upvotes: 9

Jbeltran
Jbeltran

Reputation: 111

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 $$

Upvotes: 0

Related Questions