CODEWITHSUNDEEP
sqlpostgresql
ManiP
ManiP

Reputation: 743

PostgreSQL function does not work properly

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

Upvotes: 4

Views: 277

Answers (1)

intgr
intgr

Reputation: 20486

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.

Upvotes: 1

Related Questions