Re: Help with quotes in plpgsql

[Richard Huxton <dev(at)archonet(dot)com>]

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