From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: pgsql-sql@postgresql.org
Subject: function prepared plan
Date: Fri, 21 Mar 2003 19:10:01 -0300
Message-ID: <200303211910.01785.franco@akyasociados.com.ar> (raw)
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;
RETURN NULL;
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 value FROM testTable'';
END IF;
RETURN result;
END;
' LANGUAGE 'plpgsql';
//try the function
franco=# SELECT * FROM test(true);
value
-------
one
two
three
four
(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
-------
1
2
3
4
(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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: franco@akyasociados.com.ar
Subject: Re: function prepared plan
In-Reply-To: <200303211910.01785.franco@akyasociados.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox