postgresql: syntax error at or near "IF" why?

Question

This function is a trigger that restricts grouping by series type when grouping posts.

CREATE OR REPLACE FUNCTION post_is_publish() 
    RETURNS trigger
    LANGUAGE PLPGSQL AS $body$
    BEGIN
        WITH
            psid AS (
                SELECT
                    id
                FROM
                    post_series
                WHERE
                    id = NEW.post_series_id
            )
            ,p AS (
                SELECT
                    posting_type
                FROM
                    posts
                WHERE
                    id = (SELECT id FROM psid)
            )
        IF (SELECT posting_type FROM p) = '0' THEN
            RETURN NEW;
        ELSE
            RETURN NULL;
        END IF;
    END;
$body$
;
CREATE TRIGGER post_is_publish BEFORE INSERT ON post_groups
    FOR EACH ROW
    EXECUTE FUNCTION post_is_publish();

The error is as the title says. Functions with similar syntax work, so I don't understand why this function gives an error.

version: postgresql15

・bracketedIF ((SELECT posting_type FROM p) = '0') THEN ・aliased and unaliased

Answer 1

You don't need to split the function, you just need to assign the value of posting_type to a variable and then test that variable. I made assumption that posting_type is a string.

CREATE OR REPLACE FUNCTION post_is_publish() 
    RETURNS trigger
    LANGUAGE PLPGSQL AS $body$
    DECLARE
        p_type varchar;
    BEGIN
        WITH
            psid AS (
                SELECT
                    id
                FROM
                    post_series
                WHERE
                    id = NEW.post_series_id
            )
            ,p AS (
                SELECT
                    posting_type
                FROM
                    posts
                WHERE
                    id = (SELECT id FROM psid)
            )
        SELECT posting_type INTO p_type FROM p;

        IF p_type = '0' THEN
            RETURN NEW;
        ELSE
            RETURN NULL;
        END IF;
    END;
$body$
;
CREATE TRIGGER post_is_publish BEFORE INSERT ON post_groups
    FOR EACH ROW
    EXECUTE FUNCTION post_is_publish();