Re: Help with quotes in plpgsql

Richard Ray wrote:
> It makes sense when ya'll explain it
> It never sounds that good when I'm talkin to myself
> That solves my problem but not my ignorance
> I'm still curious about how would I properly quote
> 
> create or replace function test(integer) returns setof text as $$
> declare
>   a record;
> begin
>   select into a now() - interval '$1 day';                                   ^^^^^^^^

The basic mistake is that you're assuming strings interpolate variables. 
This isn't true in plpgsql, never has been and probably never will.

So you can't do:  my_msg := 'Hello $1, how are you?';
You need:  my_msg := 'Hello ' || $1 || ', how are you?';

Don't forget most variables don't have a leading dollar sign. Imagine 
you'd defined a variable called "day" in test() - you wouldn't expect 
that to be interpolated, would you?
--   Richard Huxton  Archonet Ltd