Re: Passing varchar parameter to INTERVAL

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