Thread: Passing varchar parameter to INTERVAL
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;
ERROR: syntax error at or near "in_until"
LINE 69: ... banned_until = CURRENT_TIMESTAMP + INTERVAL in_until,
^
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
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/
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');
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');
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
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
On 7 September 2016 at 15:05, Alexander Farber <alexander.farber@gmail.com> wrote:
AlexThank youIs there please a better way here?in 9.5.4 I unfortunately get the error: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;
ERROR: syntax error at or near "in_until"
LINE 69: ... banned_until = CURRENT_TIMESTAMP + INTERVAL in_until,
^
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
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;
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 :-)