Thread: replacing expresion in plpgsql
Hi, i have a function that receives a parameter which represents days:
FUNCTION aaa_recharge_account(expdays integer)
i want to add those days to the CURRENT_DATE, but i do not know how to do it, i have tried several ways to replace that in an expresion like:
newexpdate := CURRENT_TIMESTAMP + interval '$1 days' using expdays;
(newexpdate is declared as timestamp)
and many more but none work, can someone please help me to find out how can i replace that parameter into an expression that i can add to CURRENT_TIMESTAMP or any other way that i can accomplish what i need which is to add that parameter to the current timestamp. thanks!!!
Hi,
----- Ursprüngliche Mail -----
>
> Hi, i have a function that receives a parameter which represents
> days:
>
>
> FUNCTION aaa_recharge_account(expdays integer)
>
>
> i want to add those days to the CURRENT_DATE, but i do not know how
> to do it, i have tried several ways to replace that in an expresion
> like:
assuming you want to add expdays days to the CURRENT_DATE, you can just use + arithmetic of date:
# SELECT CURRENT_DATE + 5 as in_the_future_after_5_days;
in_the_future_after_5_days
----------------------------
2013-12-21
(1 row)
>
> newexpdate := CURRENT_TIMESTAMP + interval '$1 days' using expdays;
> (newexpdate is declared as timestamp)
>
>
> and many more but none work, can someone please help me to find out
> how can i replace that parameter into an expression that i can add
> to CURRENT_TIMESTAMP or any other way that i can accomplish what i
> need which is to add that parameter to the current timestamp.
> thanks!!!
Check this out:
# select current_timestamp, current_timestamp + interval '2' day;
now | ?column?
-------------------------------+-------------------------------
2013-12-16 01:16:19.783235+01 | 2013-12-18 01:16:19.783235+01
(1 row)
AFAIK that should also be SQL compliant.
Regards,
Andreas
On 12/15/2013 4:17 PM, Andreas Brandl wrote: > select current_timestamp, current_timestamp + interval '2' day; that should be interval '2 day' (note the ' moved), and for a variable number passed as a parameter, try... select current_timestamp, current_timestamp + $1 * interval '1 day'; note this will work with values in hours, months, any unit, really. -- john r pierce 37N 122W somewhere on the middle of the left coast
Hi i m working with timestamp cause i need the time too. i tried your solution and it works perfectly, it just does not adjust to my problem. thanks a lot for the answer.
On 15 December 2013 18:17, Andreas Brandl <ml@3.141592654.de> wrote:
Hi,
----- Ursprüngliche Mail -----
>
> Hi, i have a function that receives a parameter which represents
> days:
>
>
> FUNCTION aaa_recharge_account(expdays integer)
>
>
> i want to add those days to the CURRENT_DATE, but i do not know how
> to do it, i have tried several ways to replace that in an expresion
> like:
assuming you want to add expdays days to the CURRENT_DATE, you can just use + arithmetic of date:
# SELECT CURRENT_DATE + 5 as in_the_future_after_5_days;
in_the_future_after_5_days
----------------------------
2013-12-21
(1 row)
>
> newexpdate := CURRENT_TIMESTAMP + interval '$1 days' using expdays;
> (newexpdate is declared as timestamp)
>
>
> and many more but none work, can someone please help me to find out
> how can i replace that parameter into an expression that i can add
> to CURRENT_TIMESTAMP or any other way that i can accomplish what i
> need which is to add that parameter to the current timestamp.
> thanks!!!
Check this out:
# select current_timestamp, current_timestamp + interval '2' day;
now | ?column?
-------------------------------+-------------------------------
2013-12-16 01:16:19.783235+01 | 2013-12-18 01:16:19.783235+01
(1 row)
AFAIK that should also be SQL compliant.
Regards,
Andreas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi, thank you very much, this worked flawlessly, this is the final code:
execute 'select CURRENT_TIMESTAMP + $1 * interval ''1 day''' into newexpdate using expdays;
works perfect, thanks a ton!
On 15 December 2013 18:23, John R Pierce <pierce@hogranch.com> wrote:
On 12/15/2013 4:17 PM, Andreas Brandl wrote:select current_timestamp, current_timestamp + interval '2' day;
that should be interval '2 day' (note the ' moved), and for a variable number passed as a parameter, try...
select current_timestamp, current_timestamp + $1 * interval '1 day';
note this will work with values in hours, months, any unit, really.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
John, ----- Ursprüngliche Mail ----- > On 12/15/2013 4:17 PM, Andreas Brandl wrote: > > select current_timestamp, current_timestamp + interval '2' day; > > that should be interval '2 day' (note the ' moved), and for a > variable number passed as a parameter, try... # select current_timestamp + interval '2' day; # select current_timestamp + interval '2' minute; all valid, but seems to only work in SQL (and not plpgsql) context. Regards, Andreas
thank you very much for clarifying .....
On 15 December 2013 19:02, Andreas Brandl <ml@3.141592654.de> wrote:
John,
----- Ursprüngliche Mail -----
> On 12/15/2013 4:17 PM, Andreas Brandl wrote:
> > select current_timestamp, current_timestamp + interval '2' day;
>
> that should be interval '2 day' (note the ' moved), and for a
> variable number passed as a parameter, try...
# select current_timestamp + interval '2' day;
# select current_timestamp + interval '2' minute;
all valid, but seems to only work in SQL (and not plpgsql) context.
Regards,
Andreas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
John R Pierce, 16.12.2013 01:23: >> select current_timestamp, current_timestamp + interval '2' day; > > that should be interval '2 day' (note the ' moved), and for a variable number passed as a parameter, try... Both are valid. interval '2' day is the ANSI SQL format though.
On 12/15/2013 10:54 PM, Thomas Kellerer wrote: > Both are valid. interval '2' day is the ANSI SQL format though. oh, really! ah, I stand corrected, didn't realize the units were valid keywords like that. -- john r pierce 37N 122W somewhere on the middle of the left coast
On Mon, Dec 16, 2013 at 2:08 AM, John R Pierce <pierce@hogranch.com> wrote: > On 12/15/2013 10:54 PM, Thomas Kellerer wrote: >> >> Both are valid. interval '2' day is the ANSI SQL format though. > > > oh, really! ah, I stand corrected, didn't realize the units were valid > keywords like that. Me neither. Stuff like this is black magic implemented in the parser...I always avoid it when I can. I would have done it like this: newexpdate := CURRENT_TIMESTAMP + (expdays || ' days')::interval; merlin