Re: Help with quotes in plpgsql

On Tue, 19 Dec 2006, Milen A. Radev wrote:

> Richard Ray ÿÿÿÿÿÿÿÿÿÿÿÿ:
>> How should this be properly quoted
>>
>> 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'm not having a lot of luck
>
>
> Welcome to psql 8.2.0, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help with psql commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> milen=> create or replace function test(integer) returns setof text as $$
> milen$> declare
> milen$> a record;
> milen$> begin
> milen$> select into a now() - interval '$1 day';
> milen$> return next a;
> milen$> return;
> milen$> end
> milen$> $$ language 'plpgsql';
> CREATE FUNCTION
> milen=>
>
>
>
> No problems here. What version are you using?
>

I'm using 8.1.0 but I don't think that's the problem
I have no problem creating the function but it will only substract 1 day

>
>
>From pgsql-sql-owner(at)postgresql(dot)org Tue Dec 19 17:18:38 2006
Received: from localhost (maia-4.hub.org [200.46.204.183])
by postgresql.org (Postfix) with ESMTP id 587559F946C
for <pgsql-sql-postgresql(dot)org(at)postgresql(dot)org>; Tue, 19 Dec 2006 17:18:37 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024)
with ESMTP id 29671-04 for <pgsql-sql-postgresql(dot)org(at)postgresql(dot)org>;
Tue, 19 Dec 2006 17:18:20 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.186])
by postgresql.org (Postfix) with ESMTP id EB68A9F9C35
for <pgsql-sql(at)postgresql(dot)org>; Tue, 19 Dec 2006 17:18:19 -0400 (AST)
Received: from [194.25.154.138] (helo=webserv.wug.de)
by mrelayeu.kundenserver.de (node=mrelayeu3) with ESMTP (Nemesis),
id 0MKxQS-1GwmLq13wo-0003DS; Tue, 19 Dec 2006 22:18:18 +0100
Received: from kretschmer by webserv.wug.de with local (Exim 3.36 #1)
id 1GwmLp-0008Ll-00
for pgsql-sql(at)postgresql(dot)org; Tue, 19 Dec 2006 22:18:17 +0100
Date: Tue, 19 Dec 2006 22:18:17 +0100
From: "A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Help with quotes in plpgsql
Message-ID: <20061219211817(dot)GA32044(at)a-kretschmer(dot)de>
References: <Pine(dot)LNX(dot)4(dot)64(dot)0612191404580(dot)30942(at)rray(dot)drdc(dot)mstc(dot)ms(dot)gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <Pine(dot)LNX(dot)4(dot)64(dot)0612191404580(dot)30942(at)rray(dot)drdc(dot)mstc(dot)ms(dot)gov>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the answer is "no"!
User-Agent: Mutt/1.5.9i
X-Provags-ID: kundenserver.de abuse(at)kundenserver(dot)de login:51ea9561fc3f6d9539a2acd743e4748c
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/147
X-Sequence-Number: 27103

am Tue, dem 19.12.2006, um 14:09:37 -0600 mailte Richard Ray folgendes:
> How should this be properly quoted
>
> 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'm not having a lot of luck

test=# create or replace function test(int) returns date as $$begin return current_date-($1||'days')::interval; end;$$ language plpgsql;
CREATE FUNCTION
test=# select test(4);
test
------------
2006-12-15
(1 row)

test=# select test(3);
test
------------
2006-12-16
(1 row)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net