Re: Passing varchar parameter to INTERVAL

On 7 September 2016 at 15:05, Alexander Farber <alexander(dot)farber(at)gmail(dot)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