Add variable to a Function select statement

Question

I am trying to use a variable in select statement. I am getting a the error 'column reference "purchase_rank" is ambiguous' My question is how do I insert a variable into a select statement. What am I missing? Thanks in advance.

 DECLARE

 purchase_rank int := 99; 

 SELECT a.id , purchase_rank
 FROM ABC a
 LIMIT(10)

END;
$$ LANGUAGE plpgsql;

You will need to show the entire function as well as the table description for table ABC. Somewhere you are using purchase_rank in another context and plpgsql cannot figure out which one you really want in the query. — Adrian Klaver, Jul 03 '20 at 16:38
Thanks Adrian, you are absolutely correct. I found the duplicate. Thank you! — ja11946, Jul 03 '20 at 17:11

score 0 · Answer 1 · answered Jul 10 '20 at 07:41

You are using the same variable name as column name in ABC table. That is the reason it is giving ambiguity error.

CREATE OR REPLACE FUNCTION FOO() RETURNS VOID AS 
$$
 DECLARE
 v_purchase_rank int := 99; 
 begin
 PREFORM a.id , v_purchase_rank
 FROM ABC a
 LIMIT(10) ;

END;
$$ LANGUAGE plpgsql;

try reading this answer

Add variable to a Function select statement

1 Answers1