From: Tom Lane <tgl@sss.pgh.pa.us>
To: Hector Villarreal <HVillarreal@mynewplace.com>
Cc: Milen A. Radev <milen@radev.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Help with quotes in plpgsql
Date: Tue, 19 Dec 2006 19:27:00 -0500
Message-ID: <14125.1166574420@sss.pgh.pa.us> (raw)
In-Reply-To: <8C5B026B51B6854CBE88121DBF097A866A4982@ehost010-33.exch010.intermedia.net>
References: <8C5B026B51B6854CBE88121DBF097A866A4982@ehost010-33.exch010.intermedia.net>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, HVillarreal@mynewplace.com, milen@radev.net
Subject: Re: Help with quotes in plpgsql
In-Reply-To: <14125.1166574420@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox