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

Question

I am creating a function that 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 can't understand 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';

Schwern · Accepted Answer · 2021-12-17 02:13:18Z

1

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;
$$;

edited Dec 17, 2021 at 2:13

answered Dec 17, 2021 at 2:01

Schwern

167k28 gold badges226 silver badges371 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Over a year ago

Thank you. Although now I'm wondering how this should be written to be a valid SQL function instead of plpgsql. My searches for SQL end up being MS-SQL related which I assume is not the same "sql" as the one in postgres?

Schwern Over a year ago

@Michael MS-SQL likes to refer to itself as "SQL Server". No, "SQL" refers to the SQL Standard which they theoretically follow. SQL is not a procedural language, so the standard allows different languages for SQL functions. Most define their own. MS-SQL has Transact-SQL, PostgreSQL has PL/pgSQL.

Schwern Over a year ago

@Michael And yes, you can do this as straight SQL.

UPDATE tablename SET column1 = case when p_condition1 then null else column1 end, column2 = case when p_condition2 then null else column2 end, column3 = case when p_condition3 then null else column3 end, WHERE member_id = p_id;

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related