From: | "Marcel Meulemans" <marcel(at)meulemans(dot)org> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | dynamic querys |
Date: | 2002-06-12 10:25:39 |
Message-ID: | CJECIKODEJFIJGOMIFMGIEDNCBAA.marcel@meulemans.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have the following function (below) that executes a dynamic query and returns
a varchar value. The value_table looks something like this: create table
value_table (id serial, val1 int4, val2 timestamp); If I execute the getvalue
function more than once in a row (like: select getvalue(1,'val1'); select
getvalue(1,'val2');) I get the following error+output:
NOTICE: VAL: 1234;
NOTICE: VAL: 2002-06-12 00:00:00+01;
ERROR: type of myrec.value doesn't match that when preparing the plan
To me this looks like postgres is caching the query plan and then at line 14
encountering a different datatype then expected according the plan. There is no
perpare statement, so i can't prepare the query myself and i can't find if it is
possible to write my on plans or kill the plan cache. Anybody got any
suggestions?
Tnx.
CREATE OR REPLACE FUNCTION getvalue(integer, varchar) RETURNS varchar AS '
DECLARE
id ALIAS FOR $1;
type ALIAS FOR $2;
query varchar;
myrec Record;
getvalue varchar;
BEGIN
query = ''SELECT CAST('' || quote_ident(type) || '' as varchar) as value
FROM value_table WHERE id='' || $1;
FOR myrec IN EXECUTE query LOOP
RAISE NOTICE ''VAL: %'', myrec.value;
getvalue := myrec.value;
EXIT;
END LOOP;
RETURN getvalue;
END;
' LANGUAGE 'plpgsql';
From | Date | Subject | |
---|---|---|---|
Next Message | Darren Ferguson | 2002-06-12 13:19:00 | Re: how to auto start "postmaster" |
Previous Message | Bertin, Philippe | 2002-06-12 08:45:52 | Re: automatic time zone conversion |