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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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, 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:

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

thank you very much for clarifying .....

On 15 December 2013 19:02, Andreas Brandl <ml@3.141592654.de> wrote:

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general