Re: dynamic querys

"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