function prepared plan

From: Franco Bruno Borghesi <franco(at)akyasociados(dot)com(dot)ar>
To: pgsql-sql(at)postgresql(dot)org
Subject: function prepared plan
Date: 2003-03-21 22:10:01
Message-ID: 200303211910.01785.franco@akyasociados.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;
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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2003-03-21 23:17:02 Re: Diffcult query
Previous Message Ryan 2003-03-21 20:55:04 column label