Обсуждение: replacing expresion in plpgsql

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

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

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