PostgreSQL function does not work 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 when I run the following sql, say I get 10 rows

    SELECT col 
    FROM   test
    WHERE  cola = 1;

but when i call the function i get 0 rows back, later after varying 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 strictly a SQL issue, but is this a well known PostgreSQL issue or possibly a bug?

A point of note here is that I have a number of tables with columns named 'cola', is that the reason or is there a PostgreSQL config issue?

32bit Windows version of PostgreSQL v8.3

intgr · Accepted Answer · 2011-08-14 22:28:20Z

1

Your question is rather vague, so I'm guessing in the dark here. Could you by any chance, already have a variable called "cola" in your function? When function-level variables conflict column names, the variable takes precedence and you end up with totally unexpected results to your query.

The solution is to rename the variable in your function.

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

answered Aug 14, 2011 at 22:28

intgr

20.7k6 gold badges63 silver badges73 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL function does not work properly

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related