Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id CFD489FA0B7
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 19 Dec 2006 20:27:11 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024)
	with ESMTP id 12041-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 19 Dec 2006 20:27:03 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id 0D48E9F9312
	for <pgsql-sql@postgresql.org>; Tue, 19 Dec 2006 20:27:03 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kBK0R1gv014126;
	Tue, 19 Dec 2006 19:27:01 -0500 (EST)
To: "Hector Villarreal" <HVillarreal@mynewplace.com>
cc: "Milen A. Radev" <milen@radev.net>, pgsql-sql@postgresql.org
Subject: Re: Help with quotes in plpgsql 
In-reply-to: 
 <8C5B026B51B6854CBE88121DBF097A866A4982@ehost010-33.exch010.intermedia.net>
References: 
 <8C5B026B51B6854CBE88121DBF097A866A4982@ehost010-33.exch010.intermedia.net>
Comments: In-reply-to "Hector Villarreal" <HVillarreal@mynewplace.com>
	message dated "Tue, 19 Dec 2006 16:00:00 -0800"
Date: Tue, 19 Dec 2006 19:27:00 -0500
Message-ID: <14125.1166574420@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/150
X-Sequence-Number: 27106

"Hector Villarreal" <HVillarreal@mynewplace.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