I have a function like this, which checks for appropriate account type:
CREATE OR REPLACE FUNCTION acc_is_exclusive(acc_no integer, acc_type char) RETURNS integer AS $$
BEGIN
RETURN (SELECT 1 FROM account WHERE account_no = acc_no AND account_type = acc_type);
END;
$$ LANGUAGE plpgsql;
Return type is integer, and if no record found its going to be NULL instead. Does it make sense at all? Or i need to somehow explicitly declare that return type is going to be integer or null?
You could use EXISTS logic here:
CREATE OR REPLACE FUNCTION acc_is_exclusive(acc_no integer, acc_type char) RETURNS boolean AS $$
BEGIN
RETURN EXISTS (SELECT FROM account WHERE account_no = acc_no AND account_type = acc_type);
END;
$$ LANGUAGE plpgsql;
This would return either true or false, depending on whether or not a matching record can be found.
The SQL standard says:
Every data type includes a special value, called the null value, sometimes denoted by the keyword
NULL.
So it goes without saying that you can return NULL as an integer result, because NULL is a valid value for any data type.