Home Writing a Postgres function that will use IF statement and return ID of newly inserted record

Questions

Writing a Postgres function that will use IF statement and return ID of newly inserted record

October 27, 2023

I have the following table defined as follows

create table dbo.groups
(
    groupid       uuid not null
        constraint pk__groups__149af30a383c48d4
            primary key,
    groupname     varchar(255),
    groupparentid uuid
        constraint fkgroups635827
            references groups
);

alter table dbo.groups
    owner to postgres;

I want to write a function that will insert into this table as follows:

create or replace  function spSetGroup( GroupName text,  GroupParentID  uuid ) returns uuid
    language sql
as
$$

    BEGIN
        IF $2 = '00000000-0000-0000-0000-000000000000' THEN

                INSERT INTO dbo.Groups(GroupID, GroupName,  GroupParentID)
                VALUES(uuid_generate_v4(), $1,  NULL);
        else
                INSERT INTO dbo.Groups(GroupID, GroupName, GroupParentID)
                VALUES(uuid_generate_v4(), $1, $2);

                RETURNING GroupID;
        END IF;
    END


$$;

So here, I want to check the value of $2 which is GroupParentID and based on that perform an insert into the table dbo.Groups

I also want to return the ID of the newly inserted record in the line

RETURNING GroupID

However I get the following error when I try to create this as follows:

[2023-10-27 15:17:34] [42601] ERROR: syntax error at or near "IF"
[2023-10-27 15:17:34] Position: 141

How can I get this working please?

>Solution :

In SQL you can’t use IF-THEN-ELSE constructions, but you don’t need it either: Use NULLIF()

CREATE OR REPLACE FUNCTION spsetgroup(groupname TEXT, groupparentid UUID) RETURNS UUID
    LANGUAGE sql
AS
$$
INSERT INTO dbo.groups(groupid, groupname, groupparentid)
VALUES (uuid_generate_v4(), $1, NULLIF($2, '00000000-0000-0000-0000-000000000000')) 
RETURNING GroupID;
$$;