PostgreSQL function not working properly

Question

I have a simple PostgreSQL function ... something like below

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT col FROM test WHERE cola = 1; RETURN $1; END; ' LANGUAGE plpgsql; 

the thing is, I run the next sql, let's say I get 10 rows

  SELECT col FROM test WHERE cola = 1; 

but when i call the i function, I get 0 lines back, later after changing the script I found that the following works

 CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT col FROM test t WHERE t.cola = 1; RETURN $1; END; ' LANGUAGE plpgsql; 

and returns the necessary rows.

I know this is not a SQL problem, but is this a known PostgreSQL problem or possibly an error?

Please note that I have several tables with columns named "cola", is there a reason or problem with setting up PostgreSQL?

Windows PostgreSQL Version 32 32-bit

Answer 1

Your question is rather vague, so I guess in the dark. Could you accidentally have a variable called cola in your function? When column variables at the level of functional variables conflict with column names, the variable takes precedence, and as a result you get completely unexpected results.

The solution is to rename the variable in your function.

This is a very common mistake in PL / pgSQL. PostgreSQL 9.0 and later detect such conflicts.