dynamic querys

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';

Responses

Browse pgsql-general by date

  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