Thread: 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
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
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 orterminate 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
<div class="Section1"><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">Hi Not sure about $1 parms : you may need to use a variable to set $1 to then cast it as interval : </span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">create or replace function test(integer) returns setof text as $$</span></font><p class="MsoPlainText"><font face="CourierNew" size="2"><span style="font-size: 10.0pt">declare</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> a record;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">begin</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> select into a now() - <b><span style="font-weight:bold">($1::text||'days')::interval</span></b>;</span></font><pclass="MsoPlainText"><font face="CourierNew" size="2"><span style="font-size: 10.0pt"> return next a;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> return;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">end</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">$$ language 'plpgsql';</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">-----Original Message-----<br /> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] OnBehalf Of Richard Ray<br /> Sent: Tuesday, December 19, 2006 12:10 PM<br /> To: pgsql-sql@postgresql.org<br /> Subject:[SQL] Help with quotes in plpgsql</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">How should this be properly quoted</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">create or replace function test(integer) returns setof text as $$</span></font><p class="MsoPlainText"><font face="CourierNew" size="2"><span style="font-size: 10.0pt">declare</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> a record;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">begin</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> select into a now() - interval '$1 day';</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><spanstyle="font-size: 10.0pt"> return next a;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> return;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">end</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">$$ language 'plpgsql';</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">I'm not having a lot of luck</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">Thanks</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">Richard</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt">---------------------------(end of broadcast)---------------------------</span></font><p class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 10.0pt">TIP 7: You can help support the PostgreSQL project by donating at</span></font><p class="MsoPlainText"><font face="CourierNew" size="2"><span style="font-size: 10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 10.0pt"> http://www.postgresql.org/about/donate</span></font></div>
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 > >
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 > >
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;$$ languageplpgsql; 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
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
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.orgso that your message can get through to the mailing list cleanly
"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
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 >
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