Thread: Plpgsql function and variable substitute

Hello,

I have searched the Internet for an answer but nothing works for me.

There is a plpgsql function which is used in a trigger. I need to
substitute a value "7" for "lifetime" (integer) SELECTed in a previous
query.

DECLARE
  lifetime integer;

BEGIN

    SELECT foo INTO lifetime FROM tblname;

    UPDATE ....
    SET expiretime = expiretime + interval '7 days'
    WHERE .....

  RETURN NEW;

END;

This doesn't work for me:

SET expiretime = expiretime + interval 'lifetime days'
SET expiretime = expiretime + interval || lifetime || 'days'

Thank you for your help or for a some useful link.

--
Jiri Nemec
http://www.meneashop.cz/

am  Wed, dem 16.05.2007, um 15:11:17 +0200 mailte Ji?í N?mec folgendes:
> Hello,
>
> I have searched the Internet for an answer but nothing works for me.
>
> There is a plpgsql function which is used in a trigger. I need to
> substitute a value "7" for "lifetime" (integer) SELECTed in a previous
> query.
>
> DECLARE
>   lifetime integer;
>
> BEGIN
>
>     SELECT foo INTO lifetime FROM tblname;
>
>     UPDATE ....
>     SET expiretime = expiretime + interval '7 days'
>     WHERE .....
>
>   RETURN NEW;
>
> END;
>
> This doesn't work for me:
>
> SET expiretime = expiretime + interval 'lifetime days'
> SET expiretime = expiretime + interval || lifetime || 'days'

*untested*

execute 'UPDATE ...' || lifetime || 'days'


http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


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

Jiří Němec wrote:
> SET expiretime = expiretime + interval 'lifetime days'
> SET expiretime = expiretime + interval || lifetime || 'days'

SET expiretime = expiretime + lifetime * interval '1 day'

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //