CODEWITHSUNDEEP
sqldatabasefunctionpostgresqlstored-procedures
ray
ray

Reputation: 4250

postgresql create function with multiple IF ELSE condition

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.

Upvotes: 4

Views: 18320

Answers (2)

NullEverything
NullEverything

Reputation: 460

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;

Upvotes: 6

Greg
Greg

Reputation: 6759

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;
    end if;

    SELECT pkid FROM table_2 WHERE label_id = _labelid;
    IF FOUND THEN
        result := 'true';
        RETURN;
    end if;

    SELECT pkid FROM table_3 WHERE label_id = _labelid;
    IF FOUND THEN
        result := 'true';
        RETURN;
    end if;
    result := 'false';
    RETURN;
END
$BODY$ language plpgsql;

Upvotes: 0

Related Questions