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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
