From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | How to ensure that a stored function always returns TRUE or FALSE? |
Date: | 2016-03-02 09:45:36 |
Message-ID: | CAADeyWiPSfCuozyhXD6yjizEC7VB7aYP_g5_8U6bbf09fq_OPA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Good morning,
with the following stored function I would like to validate user data:
CREATE OR REPLACE FUNCTION check_user(
in_social integer,
in_sid varchar(255),
in_auth varchar(32))
RETURNS boolean AS
$func$
SELECT MD5('secret word' || in_social || in_sid) = in_auth;
$func$ LANGUAGE sql IMMUTABLE;
I am going to call it while looping through a JSON array of objects in
another stored functions - and will RAISE EXCEPTION if it returns FALSE for
any of the JSON objects (and thus rollback the whole transaction).
I have prepared 3 simple test functions below -
CREATE OR REPLACE FUNCTION test1() RETURNS void AS
$func$
BEGIN
IF NOT check_user(42, 'user1',
'56db1046fa7b664c9b3d05bf7413552a') THEN
RAISE NOTICE 'invalid user';
ELSE
RAISE NOTICE 'valid user';
END IF;
END
$func$ LANGUAGE plpgsql;
The 1st function works as expected and prints "valid user".
CREATE OR REPLACE FUNCTION test2() RETURNS void AS
$func$
BEGIN
IF NOT check_user(42, 'user2',
'56db1046fa7b664c9b3d05bf7413552a') THEN
RAISE NOTICE 'invalid user';
ELSE
RAISE NOTICE 'valid user';
END IF;
END
$func$ LANGUAGE plpgsql;
The 2nd function works as expected and prints "invalid user".
CREATE OR REPLACE FUNCTION test3() RETURNS void AS
$func$
BEGIN
IF NOT check_user(42, 'user1', NULL) THEN
RAISE NOTICE 'invalid user';
ELSE
RAISE NOTICE 'valid user';
END IF;
END
$func$ LANGUAGE plpgsql;
The 3rd function does NOT work as expected and prints "valid user".
This happens because check_user() returns NULL instead of a boolean value.
COALESCE could be wrapped around the check_user() call in the
IF-statement... but is there maybe a nicer way to solve this problem?
Thank you
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Farber | 2016-03-02 09:47:56 | Re: Does RAISE EXCEPTION rollback previous commands in a stored function? |
Previous Message | Evgeny Morozov | 2016-03-02 09:43:51 | Re: substring on bit(n) and bytea types is slow |