Thread: function prepared plan

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.

Franco Bruno Borghesi <franco@akyasociados.com.ar> writes:
> Hi everyone! I have a little problem, and was wondering if anyone
> could help me.

Change the EXECUTEd query strings so that all the variants return TEXT,
instead of trying to cast after-the-fact.

If that doesn't seem reasonable, you might be better off working in
pltcl.  plpgsql is designed on the assumption that it can cache query
plans from one execution to the next, which is a good speed optimization
but does require a certain amount of stability of datatypes.  If you'd
prefer to kiss off speed for flexibility, pltcl is your tool.

plpython might work too, but I'm not very familiar with it and couldn't
say for certain.
        regards, tom lane

Ok Tom, I'll try casting in the testSQL function like you said... but let me ask you another thing: 
what is plpgsql exactily caching?

I mean, in the real case, the testSQL function receives about 10 arguments, and based on theese 
arguments the function generates the SQL string with very diferent grouping criterias, joins and orders.

If plpgsql is caching the plan, I don't think the same plan will work with diferent parameters. Will just
casting datatypes do the work? Or should I directly use plctl?

Thanks.


On Wednesday 21 May 2003 23:45, you wrote:
> Franco Bruno Borghesi <franco@akyasociados.com.ar> writes:
> > Hi everyone! I have a little problem, and was wondering if anyone
> > could help me.
>
> Change the EXECUTEd query strings so that all the variants return TEXT,
> instead of trying to cast after-the-fact.
>
> If that doesn't seem reasonable, you might be better off working in
> pltcl.  plpgsql is designed on the assumption that it can cache query
> plans from one execution to the next, which is a good speed optimization
> but does require a certain amount of stability of datatypes.  If you'd
> prefer to kiss off speed for flexibility, pltcl is your tool.
>
> plpython might work too, but I'm not very familiar with it and couldn't
> say for certain.
>
>             regards, tom lane

Franco Bruno Borghesi <franco@akyasociados.com.ar> writes:
> Ok Tom, I'll try casting in the testSQL function like you said... but let me ask you another thing: 
> what is plpgsql exactily caching?

The plan for the "CAST(foo AS TEXT)" expression.  Under the hood that's
a SELECT CAST($1 AS TEXT), and it has a plan.
        regards, tom lane

That's great! I'll cast everything then.

Thanks Tom.

On Thursday 22 May 2003 12:15, Tom Lane wrote:
> Franco Bruno Borghesi <franco@akyasociados.com.ar> writes:
> > Ok Tom, I'll try casting in the testSQL function like you said... but let
> > me ask you another thing: what is plpgsql exactily caching?
>
> The plan for the "CAST(foo AS TEXT)" expression.  Under the hood that's
> a SELECT CAST($1 AS TEXT), and it has a plan.
>
>             regards, tom lane