Hi everyone! I have a little problem, and was wondering if anyone could help me.
I've created a simplified working example to show you what's going on:
//table with data
CREATE TABLE testTable ( intValue INTEGER, textValue TEXT
);
INSERT INTO testTable VALUES (1, 'one');
INSERT INTO testTable VALUES (2, 'two');
INSERT INTO testTable VALUES (3, 'three');
INSERT INTO testTable VALUES (4, 'four');
//type used by my "test()" function to return the data
CREATE TYPE myType AS ( value TEXT
);
//function that returns a set records of type "myType"
CREATE OR REPLACE FUNCTION test(BOOLEAN) RETURNS SETOF myType AS '
DECLARE flag ALIAS FOR $1; mt myType%ROWTYPE; r RECORD;
BEGIN FOR r IN EXECUTE testSQL(flag) LOOP mt.value:=CAST(r.value AS TEXT); RETURN NEXT mt; END LOOP;
RETURNNULL;
END;
' LANGUAGE 'plpgsql';
//function that dynamically generates a query for my "test()" function
CREATE OR REPLACE FUNCTION testSQL(BOOLEAN) RETURNS TEXT AS '
DECLARE flag ALIAS FOR $1; result TEXT;
BEGIN IF (flag) THEN result:=''SELECT textValue AS value FROM testTable''; ELSE result:=''SELECT intValue AS
valueFROM testTable''; END IF;
RETURN result;
END;
' LANGUAGE 'plpgsql';
//try the function
franco=# SELECT * FROM test(true);value
-------onetwothreefour
(4 rows)
//try it again
franco=# SELECT * FROM test(false);
WARNING: Error occurred while executing PL/pgSQL function test
WARNING: line 9 at assignment
ERROR: type of r.value doesn't match that when preparing the plan
Of course if y reconnect to the database:
franco=# SELECT * FROM test(false);value
-------1234
(4 rows)
franco=# SELECT * FROM test(true);
WARNING: Error occurred while executing PL/pgSQL function test
WARNING: line 9 at assignment
ERROR: type of r.value doesn't match that when preparing the plan
does anyone know if there is any way to unprepare the plan between queries? I don't care about performance,
so re-preparing the plan every time is ok for me.
Thanks in advance.
PS: I know the example doesn't make much sense, but in the real life case it does.