Re: BUG #13805: plpgsql execute using expression evaluate wrong

On Tue, 08 Dec 2015 03:54:44 +0000
amutu(at)amutu(dot)com wrote:

> execute $$select ('$1 month')::interval $$ into rt2 using n;
> raise notice 'rt2 : %',rt2;
>
> execute $$select ($1::varchar || ' month')::interval $$ into rt2 using n;
> raise notice 'rt2 : %',rt2;
> end;

> NOTICE: rt2 : 1 mon-----wrong，should 4 mons
> NOTICE: rt2 : 4 mons

You may have found a bug, but if so, it is not where you think it is. The
expression: '$1 month' is a text literal. PL/PGsql does not interpolate $n
like a shell, it only does the substitution where a variable could
ordinarily exist.

However, it does seem a little odd that the literal syntax for
intervals accepts the '$':

postgres=# select '$1 month'::interval, '$4 month'::interval;
interval | interval
----------+----------
1 mon | 4 mons
(1 row)

I would have expected it to raise an error. The documentation does not shed
any light on this. Anyone?

-dg

--
David Gould 510 282 0869 daveg(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.