postgresql create function with multiple IF ELSE condition
12,097
This may be a simpler way of doing what you're are trying to do with less code.
CREATE OR REPLACE FUNCTION check_label_id_exist(_labelid integer, OUT result text) as
$BODY$
BEGIN
IF EXISTS(SELECT 1 FROM table_1 WHERE label_id = _labelid limit 1) THEN
result := 'true';
ELSEIF EXISTS(SELECT 1 FROM table_2 WHERE label_id = _labelid limit 1) THEN
result := 'true';
ELSEIF EXISTS(SELECT 1 from table_3 WHERE label_id = _labelid limit 1) THEN
result := 'true';
ELSE
result := 'false';
END IF;
RETURN;
END
$BODY$ language plpgsql;
Author by
ray
Die hard Coder but now thinking to be retired by next 5 years.
Updated on August 21, 2022Comments
-
ray over 1 year
I am creating a function in postgresql which will do something like following:
CREATE OR REPLACE FUNCTION check_label_id_exist(_labelid integer, OUT result text) AS $BODY$ DECLARE BEGIN SELECT pkid FROM table_1 WHERE label_id = _labelid; IF FOUND THEN result := 'true'; RETURN; IF NOT FOUND THEN SELECT pkid FROM table_2 WHERE label_id = _labelid; IF FOUND THEN result := 'true'; RETURN; IF NOT FOUND THEN SELECT pkid FROM table_3 WHERE label_id = _labelid; IF FOUND THEN result := 'true'; RETURN; IF NOT FOUND THEN result := 'false'; RETURN; END $BODY$ language plpgsql;
Here the function looks for data in
table_1
first. If no data then it will go to next table and so on. If any of the table has data it will break the condition and return true else finally it will return false. I think the code that I have written here is not correct. Please help to achieve my goal. -
ray over 9 yearsThanks Juan, Pretty impressively simpler way. Only I did some correction in the code to satisfy my requirement.