How to ensure that a stored function always returns TRUE or FALSE?

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

Responses

Browse pgsql-general by date

  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