pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction prepared plan
5+ messages / 2 participants
[nested] [flat]
* function prepared plan
@ 2003-03-21 22:10 Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 5+ messages in thread
From: Franco Bruno Borghesi @ 2003-03-21 22:10 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: function prepared plan
@ 2003-05-22 02:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2003-05-22 02:45 UTC (permalink / raw)
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: function prepared plan
@ 2003-05-22 15:04 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Franco Bruno Borghesi @ 2003-05-22 15:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: function prepared plan
@ 2003-05-22 15:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2003-05-22 15:15 UTC (permalink / raw)
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: function prepared plan
@ 2003-05-22 15:26 Franco Bruno Borghesi <franco@akyasociados.com.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Franco Bruno Borghesi @ 2003-05-22 15:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-05-22 15:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-21 22:10 function prepared plan Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-05-22 02:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-22 15:04 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-05-22 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-22 15:26 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
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