Question 89045 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    I created two functions that divided the language into SQL and PLPGSQL, and separated the type acquisition and conditional branching, the error disappeared.


  2. 0

    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();
    Login or Signup to reply.
  3. 0

    You don’t need a separate IF block; you can do it all in a query:

    CREATE OR REPLACE FUNCTION post_is_publish() 
RETURNS trigger
LANGUAGE PLPGSQL AS $body$
DECLARE result RECORD;
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
   MAX(CASE
        WHEN posting_type = '0' THEN NEW
        ELSE NULL END) INTO result
FROM p;
RETURN result;
END;
$body$

    See live demo.

    Not sure if you want MAX, MIN or something else, but you can adjust accordingly.

    Note also the more compact formatting that increases readability.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search