Search Postgresql Archives

Re: Passing varchar parameter to INTERVAL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On 7 September 2016 at 15:05, Alexander Farber <alexander.farber@xxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux