Обсуждение: Problems with PLPGSQL
Hi, I have a problem with quoting in one of my functions: now TIMESTAMP := ''now''; FOR myRec IN SELECT * FROM myTable WHERE job_end + ''360 Min'' > now LOOP I want to replace the 360 with the contents of a passed value but for some reason I can't quote it. ... job_end + ''$1 Min'' does not work. Could anyone help me out here ? On the subject: The whole quoting in PLPGSQL seems to create many people a headache... is there any plan to make it a bit more user friendly? Thanks Alx
Hi Alex, If you want to achieve say '360 min' as the string you will have use concatenation: FOR myRec IN SELECT * FROM myTable WHERE job_end + ($1::text || '' Min'') > now LOOP The syntax is SQL rather than PHP-like. Rgds, Jason On Thu, 6 Nov 2003 03:13 pm, Alex wrote: > Hi, > I have a problem with quoting in one of my functions: > > now TIMESTAMP := ''now''; > FOR myRec IN SELECT * FROM myTable WHERE job_end + ''360 Min'' > now LOOP > > I want to replace the 360 with the contents of a passed value > > but for some reason I can't quote it. ... job_end + ''$1 Min'' does not > work. > > Could anyone help me out here ? > > > On the subject: > The whole quoting in PLPGSQL seems to create many people a headache... > is there any plan to make it a bit more user friendly? > > Thanks > Alx > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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
On Thursday 06 November 2003 04:13, Alex wrote: > Hi, > I have a problem with quoting in one of my functions: > > now TIMESTAMP := ''now''; Your main question has been answered, but you might want now() rather than ''now''. Off the top of my head, I think the ''now'' might get compiled as a value on the first run and stay at that value. -- Richard Huxton Archonet Ltd
Hi Jason, thanks, for the reply. Tried that one but still getting an error Unable to identify an operator + for types timestamp without time zone and text .... Alex Jason Godden wrote: >Hi Alex, > >If you want to achieve say '360 min' as the string you will have use >concatenation: > >FOR myRec IN SELECT * FROM myTable WHERE job_end + ($1::text || '' Min'') > >now LOOP > >The syntax is SQL rather than PHP-like. > >Rgds, > >Jason > >On Thu, 6 Nov 2003 03:13 pm, Alex wrote: > > >>Hi, >>I have a problem with quoting in one of my functions: >> >>now TIMESTAMP := ''now''; >>FOR myRec IN SELECT * FROM myTable WHERE job_end + ''360 Min'' > now LOOP >> >>I want to replace the 360 with the contents of a passed value >> >>but for some reason I can't quote it. ... job_end + ''$1 Min'' does not >>work. >> >>Could anyone help me out here ? >> >> >>On the subject: >>The whole quoting in PLPGSQL seems to create many people a headache... >>is there any plan to make it a bit more user friendly? >> >>Thanks >>Alx >> >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 3: 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 >> >> > > >---------------------------(end of broadcast)--------------------------- >TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > > >
Hi Richard, as for the timestamp, both ways work fine, but the other problem still exists. using a ''60 Min'' works just fine, but WHERE job_end + ($1::text || '' Min'' ) > now does not. job_end is timestamp without tz Alex Richard Huxton wrote: >On Thursday 06 November 2003 04:13, Alex wrote: > > >>Hi, >>I have a problem with quoting in one of my functions: >> >>now TIMESTAMP := ''now''; >> >> > >Your main question has been answered, but you might want now() rather than >''now''. Off the top of my head, I think the ''now'' might get compiled as a >value on the first run and stay at that value. > > >
Hello Don't use now or current_time, use LOCALTIMESTAMP CURRENT_TIME and CURRENT_TIMESTAMP deliver values with time zone; LOCALTIME and LOCALTIMESTAMP deliver values without time zone. Pavel On Thu, 6 Nov 2003, Alex wrote: > Hi Richard, > as for the timestamp, both ways work fine, but the other problem still > exists. using a > ''60 Min'' works just fine, but WHERE job_end + ($1::text || '' Min'' ) > > now > does not. job_end is timestamp without tz > > Alex > > Richard Huxton wrote: > > >On Thursday 06 November 2003 04:13, Alex wrote: > > > > > >>Hi, > >>I have a problem with quoting in one of my functions: > >> > >>now TIMESTAMP := ''now''; > >> > >> > > > >Your main question has been answered, but you might want now() rather than > >''now''. Off the top of my head, I think the ''now'' might get compiled as a > >value on the first run and stay at that value. > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
On Thursday 06 November 2003 10:00, Alex wrote: > Hi Richard, > as for the timestamp, both ways work fine, but the other problem still > exists. using a > ''60 Min'' works just fine, but WHERE job_end + ($1::text || '' Min'' ) You probably want a cast: + ($1::text || '' min'')::interval -- Richard Huxton Archonet Ltd
That works. Thanks a lot. Alex Richard Huxton wrote: >On Thursday 06 November 2003 10:00, Alex wrote: > > >>Hi Richard, >>as for the timestamp, both ways work fine, but the other problem still >>exists. using a >>''60 Min'' works just fine, but WHERE job_end + ($1::text || '' Min'' ) >> >> > >You probably want a cast: > + ($1::text || '' min'')::interval > > >
On Thu, Nov 06, 2003 at 01:13:00PM +0900, Alex wrote: > On the subject: > The whole quoting in PLPGSQL seems to create many people a headache... > is there any plan to make it a bit more user friendly? Yes, there's a new cool quoting method that will make it much headache unfriendly. It will probably be there in 7.5 (not 7.4, sorry). -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Uno combate cuando es necesario... ¡no cuando está de humor! El humor es para el ganado, o para hacer el amor, o para tocar el baliset. No para combatir." (Gurney Halleck)
On Thu, Nov 06, 2003 at 18:17:52 +0900, Alex <alex@meerkatsoft.com> wrote: > Hi Jason, > thanks, for the reply. Tried that one but still getting an error > > Unable to identify an operator + for types timestamp without time zone > and text .... You probably need an explicit cast from text to interval. An untyped (unknown) string gets handled differently than one of type text.
Richard Huxton <dev@archonet.com> writes:
> On Thursday 06 November 2003 10:00, Alex wrote:
>> as for the timestamp, both ways work fine, but the other problem still
>> exists. using a
>> ''60 Min'' works just fine, but WHERE job_end + ($1::text || '' Min'' )
> You probably want a cast:
> + ($1::text || '' min'')::interval
This is pretty much the hard way, though. A better idea is to use the
number-times-interval operator:
timestampvalue + $1 * '1 min'::interval;
Easier to write and faster.
regards, tom lane