Обсуждение: dynamic querys

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

"Marcel Meulemans" <marcel@meulemans.org> writes:
> 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

This seems to be a bug partly induced by the "DISABLE_STRING_HACKS" code
in parse_target.c.  There is no timestamp-to-varchar conversion
function, so said code silently substitutes timestamp-to-text.  Which
causes plpgsql to complain that the plan isn't working anymore.

The DISABLE_STRING_HACKS code is surely broken; it should be attaching a
RelabelType node so that the result is actually labeled with the type
it's supposedly getting coerced to.

plpgsql has perhaps got a problem here as well, since it's effectively
assuming that an EXECUTE'd query will yield the same column datatypes
every time through.  Is that a reasonable assumption?  If not, how
fixable is it?

In the meantime, Marcel, I'd counsel using text not varchar in your
function.

            regards, tom lane