Passing varchar parameter to INTERVAL

Hello,
On Wed, 2016-09-07 at 15:05 +0200, Alexander Farber wrote:

Good afternoon,

when trying to create a custom function to temporary ban a user:

CREATE OR REPLACE FUNCTION words_ban_user(
        IN in_uid integer,
        IN in_until varchar,    -- '1 week' OR '1 month' OR '1 year'
        IN in_reason varchar)
        RETURNS void AS
$func$
BEGIN
........
        UPDATE words_users SET
                banned_until  = CURRENT_TIMESTAMP + INTERVAL
in_until,
                banned_reason = in_reason,
                vip_until     = vip_until + INTERVAL in_until,    --
for paying user
                grand_until   = grand_until + INTERVAL in_until
        WHERE uid = in_uid;

END
$func$ LANGUAGE plpgsql;

in 9.5.4 I unfortunately get the error:

ERROR:  syntax error at or near "in_until"
LINE 69: ...       banned_until = CURRENT_TIMESTAMP + INTERVAL
in_until,
                                                               ^
Is there please a better way here?

Thank you
Alex

I think the interval values need to be quoted.
In any event I always use the P or T syntax. 'P1D' means add one day,
etc.

HTH,
Rob

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

On Wed, Sep 07, 2016 at 03:05:38PM +0200, Alexander Farber wrote:

ERROR: syntax error at or near "in_until"
LINE 69: ... banned_until = CURRENT_TIMESTAMP + INTERVAL in_until,
^
Is there please a better way here?

Why don't you simply make in_util parameter interval? What is the point
of accepting varchar, if you're unconditionally casting it to interval
anyway?

Having said that, the error looks rather strange for this.

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/

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

Hello Rob,

On Wed, Sep 7, 2016 at 3:24 PM, rob stone <floriparob@gmail.com> wrote:

I think the interval values need to be quoted.
In any event I always use the P or T syntax. 'P1D' means add one day,

unfortunately, I can not call INTERVAL 'in_until', that wouldn't work.

Also 'P1D' vs. '1 day' seems to be just a matter of taste.

Finally, to provide more context to my question -

I would prefer to call my custom function as

select words_ban_user(1, '1 day', 'attacking other users');

and not as

select words_ban_user(1, CURRENT_TIMESTAMP + '1 day', 'attacking other
users');

because additionally to the temporary ban I would like to prolong
VIP-periods for paying users (to avoid discussions) - and that would be
more difficult in the latter case (would require more date acrobatics)...

Regards
Alex

On 09/07/2016 06:05 AM, Alexander Farber wrote:

Good afternoon,

when trying to create a custom function to temporary ban a user:

CREATE OR REPLACE FUNCTION words_ban_user(
IN in_uid integer,
IN in_until varchar, -- '1 week' OR '1 month' OR '1 year'
IN in_reason varchar)
RETURNS void AS
$func$
BEGIN
........
UPDATE words_users SET
banned_until = CURRENT_TIMESTAMP + INTERVAL in_until,
banned_reason = in_reason,
vip_until = vip_until + INTERVAL in_until, -- for
paying user
grand_until = grand_until + INTERVAL in_until
WHERE uid = in_uid;

END
$func$ LANGUAGE plpgsql;

in 9.5.4 I unfortunately get the error:

ERROR: syntax error at or near "in_until"
LINE 69: ... banned_until = CURRENT_TIMESTAMP + INTERVAL in_until,
^
Is there please a better way here?

DO
$$
DECLARE
in_until varchar := '1 day';
banned_until timestamp;
BEGIN
banned_until = CURRENT_TIMESTAMP + in_until::interval;
RAISE NOTICE '%', banned_until;
END$$;

NOTICE: 2016-09-08 06:50:14.051719

When I did it your way I got:

test=> DO
$$
DECLARE
in_until varchar := '1 day';
banned_until timestamp;
BEGIN
banned_until = CURRENT_TIMESTAMP + INTERVAL in_until;
END$$;
ERROR: column "interval" does not exist
LINE 1: SELECT CURRENT_TIMESTAMP + INTERVAL in_until
^
QUERY: SELECT CURRENT_TIMESTAMP + INTERVAL in_until
CONTEXT: PL/pgSQL function inline_code_block line 6 at assignment

Thank you
Alex

--
Adrian Klaver
adrian.klaver@aklaver.com

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

Alexander Farber <alexander.farber@gmail.com> writes:

unfortunately, I can not call INTERVAL 'in_until', that wouldn't work.

No, because the syntax TYPENAME 'LITERAL' only works for simple string
literals. What you want here is a run-time cast, either
CAST(in_until AS interval) or in_until::interval.

I would prefer to call my custom function as
select words_ban_user(1, '1 day', 'attacking other users');

That is not a reason not to declare the argument as interval.

and not as
select words_ban_user(1, CURRENT_TIMESTAMP + '1 day', 'attacking other
users');

Hm? That would be passing a timestamp not an interval.

regards, tom lane

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

On 7 September 2016 at 15:05, Alexander Farber <alexander.farber@gmail.com>
wrote:

Good afternoon,

when trying to create a custom function to temporary ban a user:

CREATE OR REPLACE FUNCTION words_ban_user(
IN in_uid integer,
IN in_until varchar, -- '1 week' OR '1 month' OR '1 year'
IN in_reason varchar)
RETURNS void AS
$func$
BEGIN
........
UPDATE words_users SET
banned_until = CURRENT_TIMESTAMP + INTERVAL in_until,
banned_reason = in_reason,
vip_until = vip_until + INTERVAL in_until, -- for
paying user
grand_until = grand_until + INTERVAL in_until
WHERE uid = in_uid;

END
$func$ LANGUAGE plpgsql;

in 9.5.4 I unfortunately get the error:

ERROR: syntax error at or near "in_until"
LINE 69: ... banned_until = CURRENT_TIMESTAMP + INTERVAL in_until,
^
Is there please a better way here?

Thank you
Alex

First it doesn't make sense to use IN parameters. Every parameter is IN
parameter by default if it's not explicitly OUT parameter.
And I think the :: casting operator is more straightforward.

If I wrote this function it would look like this:

CREATE OR REPLACE FUNCTION words_ban_user(
in_uid integer,
in_until varchar, -- '1 week' OR '1 month' OR '1 year'
in_reason varchar)
RETURNS void AS
$func$
BEGIN

UPDATE words_users SET
banned_until = CURRENT_TIMESTAMP + in_until::interval,
banned_reason = in_reason,
vip_until = vip_until + in_until::interval, -- for
paying user
grand_until = grand_until + in_until::interval
WHERE uid = in_uid;

END
$func$ LANGUAGE plpgsql;

And as the others pointed this out you could declare in_until as interval,
skip the whole casting and still could call the function as
select words_ban_user(1, '1 day', 'attacking other users')

Regards,
Sándor

Thank you, this works well now and comments about IN is appreciated too :-)