Message-ID: <45894822.2060605@archonet.com>
Date: Wed, 20 Dec 2006 14:26:42 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.8 (X11/20061115)
MIME-Version: 1.0
To: Richard Ray <rray@mstc.state.ms.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with quotes in plpgsql
References: 
 <8C5B026B51B6854CBE88121DBF097A866A4982@ehost010-33.exch010.intermedia.net>
	<14125.1166574420@sss.pgh.pa.us>
	<Pine.LNX.4.64.0612200800160.24340@rray.drdc.mstc.ms.gov>
In-Reply-To: <Pine.LNX.4.64.0612200800160.24340@rray.drdc.mstc.ms.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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