Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problems with PLPGSQL

Problems with PLPGSQL

From
Alex
Date:
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

Re: Problems with PLPGSQL

From
Jason Godden
Date:
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

Re: Problems with PLPGSQL

From
Richard Huxton
Date:
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

Re: Problems with PLPGSQL

From
Alex
Date:
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
>
>
>
>

Re: Problems with PLPGSQL

From
Alex
Date:
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.
>
>
>

Re: Problems with PLPGSQL

From
Pavel Stehule
Date:
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
>

Re: Problems with PLPGSQL

From
Richard Huxton
Date:
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

Re: Problems with PLPGSQL

From
Alex
Date:
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
>
>
>

Re: Problems with PLPGSQL

From
Alvaro Herrera
Date:
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)

Re: Problems with PLPGSQL

From
Bruno Wolff III
Date:
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.

Re: Problems with PLPGSQL

From
Tom Lane
Date:
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