function prepared plan
От | Franco Bruno Borghesi |
---|---|
Тема | function prepared plan |
Дата | |
Msg-id | 200303211910.01785.franco@akyasociados.com.ar обсуждение исходный текст |
Ответы |
Re: function prepared plan
|
Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления: