Re: Help with quotes in plpgsql

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';
return next a;
return;
end
$$ language 'plpgsql';

I got the usage example for interval from
http://www.postgresql.org/docs/8.1/interactive/functions-datetime.html

Thanks
Richard

On Tue, 19 Dec 2006, Tom Lane wrote:

> "Hector Villarreal" <HVillarreal(at)mynewplace(dot)com> writes:
>> select into a now() - ($1::text||'days')::interval;
>
> People keep suggesting variants of that as ways to convert numeric
> values to intervals, but it's really extremely bad practice. Much
> better is to use number-times-interval multiplication:
>
> select into a now() - $1 * '1 day'::interval;
>
> This is less typing, at least as easy to understand, more flexible
> (you can use any scale factor you want), and considerably more
> efficient. The first way involves coercing the integer to text,
> then text-concatenating that with a constant, then applying
> interval_in which does a fairly nontrivial parsing process.
> The second way is basically just a multiplication, because
> '1 day'::interval is already a constant value of type interval.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>