Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 0F6EA9F9F46 for ; Wed, 20 Dec 2006 10:11:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 68958-07 for ; Wed, 20 Dec 2006 10:11:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from itspmx2.state.ms.us (itspmx2.state.ms.us [205.144.224.112]) by postgresql.org (Postfix) with ESMTP id 5CE0E9F9C35 for ; Wed, 20 Dec 2006 10:11:12 -0400 (AST) Received: from rray.drdc.mstc.ms.gov ([10.3.192.2]) by itspmx2.state.ms.us (8.13.4/8.13.4) with ESMTP id kBKEAtDs002919 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Wed, 20 Dec 2006 08:11:10 -0600 (CST) Received: from rray.drdc.mstc.ms.gov (localhost.localdomain [127.0.0.1]) by rray.drdc.mstc.ms.gov (8.13.6/8.13.6) with ESMTP id kBKEAsZh025656 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Wed, 20 Dec 2006 08:10:54 -0600 Received: from localhost (rray@localhost) by rray.drdc.mstc.ms.gov (8.13.6/8.13.6/Submit) with ESMTP id kBKEArPQ025653 for ; Wed, 20 Dec 2006 08:10:54 -0600 X-Authentication-Warning: rray.drdc.mstc.ms.gov: rray owned process doing -bs Date: Wed, 20 Dec 2006 08:10:53 -0600 (CST) From: Richard Ray X-X-Sender: rray@rray.drdc.mstc.ms.gov To: pgsql-sql@postgresql.org Subject: Re: Help with quotes in plpgsql In-Reply-To: <14125.1166574420@sss.pgh.pa.us> Message-ID: References: <8C5B026B51B6854CBE88121DBF097A866A4982@ehost010-33.exch010.intermedia.net> <14125.1166574420@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/156 X-Sequence-Number: 27112 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" 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 >