Why am I getting a syntax error when using an IF statement in my Postgres function?-CodePudding

I am creating a function that will allow me to conditionally update specific columns in a table. However, I get an error indicating that there is a syntax error at or near "IF" when I try to run the following code. I'm a bit new to Postgres so it's quite possible I'm not understanding some concept/syntax thing in Postgres. Can someone help me by pointing out the mistake I must be making?

CREATE OR REPLACE FUNCTION profiles.do_something(
        p_id UUID,
        p_condition1 BOOLEAN,
        p_condition2 BOOLEAN,
        p_condition3 BOOLEAN
    ) 
RETURNS void AS $$
BEGIN

    IF p_condition1 IS TRUE THEN
        UPDATE tablename SET column1 = null WHERE member_id = p_id;
    END IF;

    IF p_condition2 IS TRUE THEN
        UPDATE tablename SET column2 = null WHERE member_id = p_id;
    END IF;

    IF p_condition3 IS TRUE THEN
        UPDATE tablename SET column3 = null WHERE member_id = p_id;
    END IF;

END;
$$ LANGUAGE 'sql';

CodePudding user response：

tl;dr $$ LANGUAGE 'plpgsql'

$$ LANGUAGE 'sql';
            ^^^^^

You're tell it to parse the body of the function as sql. In SQL, begin is a statement which starts a transaction.

create or replace function test1()
returns void
language sql
as $$
-- In SQL, begin starts a transaction.
-- note the ; to end the statement.
begin;
    -- Do some valid SQL.
    select 1;
-- In SQL, end ends the transaction.
end;
$$;

In SQL you wrote begin if ... which is a syntax error.

The language you're using is plpgsql. In plpgsql, begin is a keyword which starts a block.

create or replace function test1()
returns void
language plpgsql
as $$
-- In PL/pgSQL, begin starts a block
-- note the lack of ;
begin
    -- Do some valid SQL.
    select 1;
-- In PL/pgSQL, end ends the block
end;
$$;