pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with quotes in plpgsql
11+ messages / 6 participants
[nested] [flat]
* Help with quotes in plpgsql
@ 2006-12-19 20:09 Richard Ray <rray@mstc.state.ms.us>
0 siblings, 3 replies; 11+ messages in thread
From: Richard Ray @ 2006-12-19 20:09 UTC (permalink / raw)
To: pgsql-sql
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
Thanks
Richard
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Help with quotes in plpgsql
@ 2006-12-19 20:17 Hector Villarreal <HVillarreal@mynewplace.com>
parent: Richard Ray <rray@mstc.state.ms.us>
2 siblings, 1 reply; 11+ messages in thread
From: Hector Villarreal @ 2006-12-19 20:17 UTC (permalink / raw)
To: Richard Ray <rray@mstc.state.ms.us>; pgsql-sql
Hi Not sure about $1 parms : you may need to use a variable to set $1 to
then cast it as interval :
create or replace function test(integer) returns setof text as $$
declare
a record;
begin
select into a now() - ($1::text||'days')::interval;
return next a;
return;
end
$$ language 'plpgsql';
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Richard Ray
Sent: Tuesday, December 19, 2006 12:10 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Help with quotes in plpgsql
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
Thanks
Richard
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Help with quotes in plpgsql
@ 2006-12-19 20:20 Jim Buttafuoco <jim@contactbda.com>
parent: Richard Ray <rray@mstc.state.ms.us>
2 siblings, 0 replies; 11+ messages in thread
From: Jim Buttafuoco @ 2006-12-19 20:20 UTC (permalink / raw)
To: 'Richard Ray' <rray@mstc.state.ms.us>; pgsql-sql
Try
select into a now() - interval ($1 || ' day')
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Richard Ray
Sent: Tuesday, December 19, 2006 3:10 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Help with quotes in plpgsql
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
Thanks
Richard
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Help with quotes in plpgsql
@ 2006-12-19 20:22 Milen A. Radev <milen@radev.net>
parent: Richard Ray <rray@mstc.state.ms.us>
2 siblings, 1 reply; 11+ messages in thread
From: Milen A. Radev @ 2006-12-19 20:22 UTC (permalink / raw)
To: pgsql-sql
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?
--
Milen A. Radev
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Help with quotes in plpgsql
@ 2006-12-19 21:02 Richard Ray <rray@mstc.state.ms.us>
parent: Hector Villarreal <HVillarreal@mynewplace.com>
0 siblings, 0 replies; 11+ messages in thread
From: Richard Ray @ 2006-12-19 21:02 UTC (permalink / raw)
To: pgsql-sql
On Tue, 19 Dec 2006, Hector Villarreal wrote:
> Hi Not sure about $1 parms : you may need to use a variable to set $1 to
> then cast it as interval :
>
>
>
>
>
> create or replace function test(integer) returns setof text as $$
>
> declare
>
> a record;
>
> begin
>
> select into a now() - ($1::text||'days')::interval;
>
> return next a;
>
> return;
>
> end
>
> $$ language 'plpgsql';
>
This works quite well
I never looked at it that way
Thanks
>
>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Richard Ray
> Sent: Tuesday, December 19, 2006 12:10 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Help with quotes in plpgsql
>
>
>
> 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
>
> Thanks
>
> Richard
>
>
>
> ---------------------------(end of broadcast)---------------------------
>
> TIP 7: You can help support the PostgreSQL project by donating at
>
>
>
> http://www.postgresql.org/about/donate
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Help with quotes in plpgsql
@ 2006-12-19 21:02 Richard Ray <rray@mstc.state.ms.us>
parent: Milen A. Radev <milen@radev.net>
0 siblings, 1 reply; 11+ messages in thread
From: Richard Ray @ 2006-12-19 21:02 UTC (permalink / raw)
To: pgsql-sql
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@postgresql.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.org@postgresql.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.org@postgresql.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@postgresql.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@postgresql.org; Tue, 19 Dec 2006 22:18:17 +0100
Date: Tue, 19 Dec 2006 22:18:17 +0100
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Help with quotes in plpgsql
Message-ID: <20061219211817.GA32044@a-kretschmer.de>
References: <Pine.LNX.4.64.0612191404580.30942@rray.drdc.mstc.ms.gov>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <Pine.LNX.4.64.0612191404580.30942@rray.drdc.mstc.ms.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@kundenserver.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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Help with quotes in plpgsql
@ 2006-12-19 22:54 Milen A. Radev <milen@radev.net>
parent: Richard Ray <rray@mstc.state.ms.us>
0 siblings, 1 reply; 11+ messages in thread
From: Milen A. Radev @ 2006-12-19 22:54 UTC (permalink / raw)
To: pgsql-sql
Richard Ray написа:
> 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
Sorry about that - I have not understand your problem.
In addition to the solution already proposed you could use "EXECUTE".
See more info here -
http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
.
--
Milen A. Radev
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Help with quotes in plpgsql
@ 2006-12-20 00:00 Hector Villarreal <HVillarreal@mynewplace.com>
parent: Milen A. Radev <milen@radev.net>
0 siblings, 1 reply; 11+ messages in thread
From: Hector Villarreal @ 2006-12-20 00:00 UTC (permalink / raw)
To: Milen A. Radev <milen@radev.net>; pgsql-sql
In case it is a version issue:
This should always work regardless of version:
Just cast the $1 variable as text followed by interval:
create or replace function test(integer) returns setof text as $$
declare
a record;
begin
select into a now() - ($1::text||'days')::interval;
return next a;
return;
end
$$ language 'plpgsql';
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Milen A. Radev
Sent: Tuesday, December 19, 2006 2:54 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help with quotes in plpgsql
Richard Ray написа:
> 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
Sorry about that - I have not understand your problem.
In addition to the solution already proposed you could use "EXECUTE".
See more info here -
http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
.
--
Milen A. Radev
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Help with quotes in plpgsql
@ 2006-12-20 00:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hector Villarreal <HVillarreal@mynewplace.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2006-12-20 00:27 UTC (permalink / raw)
To: Hector Villarreal <HVillarreal@mynewplace.com>; +Cc: Milen A. Radev <milen@radev.net>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Help with quotes in plpgsql
@ 2006-12-20 14:10 Richard Ray <rray@mstc.state.ms.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Richard Ray @ 2006-12-20 14:10 UTC (permalink / raw)
To: pgsql-sql
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@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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Help with quotes in plpgsql
@ 2006-12-20 14:26 Richard Huxton <dev@archonet.com>
parent: Richard Ray <rray@mstc.state.ms.us>
0 siblings, 0 replies; 11+ messages in thread
From: Richard Huxton @ 2006-12-20 14:26 UTC (permalink / raw)
To: Richard Ray <rray@mstc.state.ms.us>; +Cc: pgsql-sql
Richard Ray wrote:
> 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';
^^^^^^^^
The basic mistake is that you're assuming strings interpolate variables.
This isn't true in plpgsql, never has been and probably never will.
So you can't do:
my_msg := 'Hello $1, how are you?';
You need:
my_msg := 'Hello ' || $1 || ', how are you?';
Don't forget most variables don't have a leading dollar sign. Imagine
you'd defined a variable called "day" in test() - you wouldn't expect
that to be interpolated, would you?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-12-20 14:26 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-19 20:09 Help with quotes in plpgsql Richard Ray <rray@mstc.state.ms.us>
2006-12-19 20:17 ` Hector Villarreal <HVillarreal@mynewplace.com>
2006-12-19 21:02 ` Richard Ray <rray@mstc.state.ms.us>
2006-12-19 20:20 ` Jim Buttafuoco <jim@contactbda.com>
2006-12-19 20:22 ` Milen A. Radev <milen@radev.net>
2006-12-19 21:02 ` Richard Ray <rray@mstc.state.ms.us>
2006-12-19 22:54 ` Milen A. Radev <milen@radev.net>
2006-12-20 00:00 ` Hector Villarreal <HVillarreal@mynewplace.com>
2006-12-20 00:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-20 14:10 ` Richard Ray <rray@mstc.state.ms.us>
2006-12-20 14:26 ` Richard Huxton <dev@archonet.com>
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