postgresql create function with multiple IF ELSE condition

Question

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.

@NeilMcGuigan Union could be a good idea. But I am not good on that. But obviously I would like to try. Can you please give me a sample. I just want to return true or false. If there have any result for any of the queries it will return true otherwise false. — ray
– ray, Commented Aug 8, 2014 at 18:24

NullEverything · Accepted Answer · 2014-08-12 00:41:05Z

6

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;

edited Aug 12, 2014 at 0:41

answered Aug 10, 2014 at 0:13

NullEverything

4602 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ray Over a year ago

Thanks Juan, Pretty impressively simpler way. Only I did some correction in the code to satisfy my requirement.

Greg · Accepted Answer · 2014-08-08 18:24:01Z

0

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;

answered Aug 8, 2014 at 18:24

Greg

6,7892 gold badges29 silver badges43 bronze badges

Collectives™ on Stack Overflow

postgresql create function with multiple IF ELSE condition

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related