From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Organization: AK y Asociados S.R.L.
To: pgsql-sql@postgresql.org
Subject: function prepared plan
Date: Fri, 21 Mar 2003 19:10:01 -0300
User-Agent: KMail/1.5.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200303211910.01785.franco@akyasociados.com.ar>

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.