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')