How can i combine multiple functions into one in postgresql?

Question

I wrote a series of functions updating a progress column in my project, I wonder if this can be combined into a single function. I tried it, using SELECT * FROM schema.table; IF, ELSE IF ... THEN UPDATE column SET progress = xx however all i got was syntax errors on various lines, with which i couldn't figure out what i did wrong. I would appreciate ideas as to what i did wrong or how i could achieve a single function in a different way, THANKS GUYS :)

This is the code, that worked, but i want to shorten:

CREATE OR REPLACE FUNCTION progress_update25() RETURNS void AS $$
BEGIN
    UPDATE schema.table SET progress = 25 WHERE on_side_inspection = 'done';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION progress_update50() RETURNS void AS $$
BEGIN
    UPDATE schema.table SET progress = 50 WHERE statement = 'requested' OR statement = 'received';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION progress_update75() RETURNS void AS $$
BEGIN
    UPDATE schema.table SET progress = 75 WHERE 
        permit_01 = 'requested' OR permit_01 = 'permitted' OR permit_01 = 'n/a'
        AND permit_02 = 'requested' OR permit_02 = 'permitted' OR permit_02 = 'n/a'
        AND permit_03 = 'requested' OR permit_03 = 'permitted' OR permit_03 = 'n/a'
        AND permit_04 = 'requested' OR permit_04 = 'permitted' OR permit_04 = 'n/a';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION progress_update90() RETURNS void AS $$
BEGIN
    UPDATE schema.table SET progress = 90 WHERE permits_complete = 'complete';
END;
$$ LANGUAGE plpgsql;

    
CREATE OR REPLACE FUNCTION progress_update100() RETURNS void AS $$
BEGIN
    UPDATE schema.table SET progress = 100 WHERE documentation = 'submitted';
END;
$$ LANGUAGE plpgsql;

This is the code, thats not working:

CREATE OR REPLACE FUNCTION progress_update() RETURNS void AS $$
BEGIN
    SELECT * FROM schema.table;
        IF on_side_inspection = 'done' 
        THEN UPDATE table SET progress = 25;

        ELSE IF statement = 'requested' OR statement = 'received' 
        THEN UPDATE table SET progress = 50;

        ELSE IF permit_01 = 'requested' OR permit_01 = 'permitted' OR permit_01 = 'n/a'
        AND permit_02 = 'requested' OR permit_02 = 'permitted' OR permit_02 = 'n/a'
        AND permit_03 = 'requested' OR permit_03 = 'permitted' OR permit_03 = 'n/a'
        AND permit_04 = 'requested' OR permit_04 = 'permitted' OR permit_04 = 'n/a'
        THEN UPDATE table SET progress = 75;

        ELSE IF permits_complete = 'complete'
        THEN UPDATE table SET progress = 90;

        ELSE IF documentation = 'submitted'
        THEN UPDATE table SET progress = 100;

        END IF;
END;
$$ LANGUAGE plpgsql;

What is your definition of "not working" ? What happens when you execute the code? — Frank Heikens, Sep 05 '22 at 12:45
https://www.postgresql.org/docs/current/plpgsql.html might be a good starting point. By the way, numbered column names (permit_01, permit_02, etc.) are usually a symptom of bad database design. — Frank Heikens, Sep 05 '22 at 13:43

score 0 · Answer 1 · answered Sep 05 '22 at 13:34

0

There are lot of issues - undeclared variables, missing INTO clause or badly used IF statement:

You do:

IF ... THEN
ELSE IF THEN
END IF;

It can work in some other languages, but not in Postgres. It should to be

IF ... THEN
ELSEIF ... THEN
END IF;

answered Sep 05 '22 at 13:34

Pavel Stehule

42,331
5
91
94

How can i combine multiple functions into one in postgresql?

1 Answers1