Search Postgresql Archives

plpgsql cursor reuse

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Came across this problem when trying to assign to a variable a field from a record that could come from multiple cursors. PG throws an error – “

ERROR:  type of parameter 7 (bigint) does not match that when preparing the plan (unknown)”. If I make the null column in c1 null::bigint to match cursor c2, it works fine.

 

Where is this plan coming from? Why would it match c1 to a plan coming from c2? In reality, the two cursors in question are wildly different- a join of about 10 completely different tables. When I saw the text of the error I was a bit concerned that it was being overly flexible in matching the current cursor to another.

 

It errors out on the assignment to I, not the fetch. (maybe the fetch isn’t actually being done until the data in r is used).

 

 

 

 

CREATE OR REPLACE FUNCTION demo.test_cursor_bug ( a IN integer ) RETURNS void AS

$BODY$

DECLARE

                c1 cursor FOR SELECT 1 as shipmentid, null as olmid;

                c2 cursor FOR SELECT 2 as shipmentid, 32::bigint as olmid;

                r record;

                i bigint;

 

BEGIN

                IF ( a = 0 ) THEN

                                open c1;

                                fetch c1 INTO r;

                                close c1;

                END IF;

                IF ( a = 1 ) THEN

                                open c2;

                                fetch c2 INTO r;

                                close c2;

                END IF;

                i := r.olmid;

 

END;

$BODY$

LANGUAGE plpgsql;

 

 

select demo.test_cursor_bug(0);

select demo.test_cursor_bug(1);

 

 

 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux