current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
@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 2 days ago

2 Answers 2

up vote 1 down vote accepted

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) THEN

        result := 'true';

    ELSEIF EXISTS(SELECT 1 FROM table_2 WHERE label_id = _labelid) THEN

        result := 'true';

    ELSEIF EXISTS(SELECT 1 from table_3 WHERE label_id = _labelid) THEN

        result := 'true';

    ELSE

        result := 'false';
    END IF;

    RETURN;
END
$BODY$ language plpgsql;
share|improve this answer
    
Thanks Juan, Pretty impressively simpler way. Only I did some correction in the code to satisfy my requirement. –  ray 12 hours ago
up vote 0 down vote 
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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.