How to check if values exists in a table - postgres PLPGSQL

Question

ERD

users
id
name

groups
id
name

users_in_groups
user_id
group_id

Problem summary

I'm writing a stored procedure in postgres that recieves a group name and users array and adds users to the group, and I want to assert first that the users exists in users - because I want to raise a custom error so I can catch it my server (if I rely on the default errors like - FK violation, I cannot classify it specifically enough in my server).

The stored procedure

CREATE FUNCTION add_users_to_group(group_name text, users text[])
RETURNS VOID AS $$
DECLARE
  does_all_users_exists boolean;
BEGIN
  SELECT exist FROM (
    WITH to_check (user_to_check) as (select unnest(users))
    SELECT bool_and(EXISTS (
       SELECT * FROM users where id = to_check.user_to_check
    )) as exist from to_check) as existance INTO does_all_users_exists;

  IF NOT does_all_users_exists THEN
    RAISE EXCEPTION '%', does_all_users_exists USING ERRCODE = 'XXXXX';

  -- TODO: loop through each user and insert into users_in_groups
END;
$$ LANGUAGE PLPGSQL VOLATILE STRICT SECURITY INVOKER;

The problem

When I execute the function with users that exists in the users table, I get the error I throw and the message is: f (so my variable was false), but when I run only the query that gives me the existance of the all the users:

WITH to_check (user_to_check) as (select unnest(users))
    SELECT bool_and(EXISTS (
       SELECT * FROM users where id = to_check.user_to_check
    )) as exist from to_check

I get true. but I get it inside a table like so:

#	exist (boolean)
1	true

so I guess I need to extract the true somehow.

anyway I know there is a better solution for validating the existance before insert, you are welcome to suggest.

Why is it such a problem that a user already exists that you have exit using an exception? You could also ignore the users that already exist, and maybe return some message, but without an exception. But it all depends on the logic you need — Frank Heikens
– Frank Heikens, Commented Jan 11, 2021 at 19:12
The problem is not that the user already exists, but the user might not be in the users table, and I need to assert that - so I won't get this error: insert or update on table "users_in_groups" violates foreign key constraint "users_in_groups_id_fkey". I don't want to regex this error in my server because I don't want to rely that the table names and constraints will not change. — stav bitanski
– stav bitanski, Commented Jan 12, 2021 at 7:10
Using a different function that does all the checks in one blow, you can avoid the constraint violations. It's basically just one query, wrapped in a function. — Frank Heikens
– Frank Heikens, Commented Jan 12, 2021 at 11:32

Gordon Linoff · Accepted Answer · 2021-01-11 16:22:05Z

1

Your logic seems unnecessarily complex. You can just check if any user doesn't exist using NOT EXISTS:

SELECT 1
FROM UNNEST(users) user_to_check
WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = user_to_check)

answered Jan 11, 2021 at 16:22

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

Frank Heikens · Accepted Answer · 2021-01-12 11:30:59Z

When you want to avoid issues with unique and foreign key constraints, you can SELECT and INSERT the records that you need for the next step. And you can do this for both tables (users and groups) in a single query, including the INSERT in users_in_groups:

CREATE FUNCTION add_users_to_group(group_name text, users text[])
RETURNS VOID AS $$

    WITH    id_users AS (
        -- get id's for existing users:
        SELECT  id, name
        FROM    users
        WHERE   name =any($2)
    ), dml_users AS (
        -- create id's for the new users:
        INSERT  INTO users (name)
        SELECT  s.name
        FROM    unnest($2) s(name)
        WHERE   NOT EXISTS(SELECT 1 FROM id_users i WHERE i.name = s.name)
        -- Just to be sure, not sure you want this:
        ON conflict do NOTHING
        -- Result:
        RETURNING id
    ),  id_groups AS (
        -- get id for an existing group:
        SELECT  id, name
        FROM    users
        WHERE   name = $1
    ), dml_group AS (
        -- create id's for the new users:
        INSERT  INTO groups (name)
        SELECT  s.name
        FROM    (VALUES($1)) s(name)
        WHERE   NOT EXISTS(SELECT 1 FROM id_groups i WHERE i.name = s.name)
        -- Just to be sure, not sure you want this:
        ON conflict do NOTHING
        -- Result:
        RETURNING id
    )
    INSERT INTO users_in_groups(user_id, group_id)
    SELECT  user_id, group_id
    FROM    (
        -- get all user-id's
        SELECT  id  FROM    dml_users
        UNION
        SELECT  id  FROM    id_users
        ) s1(user_id)
        -- get all group-id's
        ,   (
        SELECT  id  FROM    dml_group
        UNION
        SELECT  id  FROM    id_groups   
        ) s2(group_id);

$$ LANGUAGE sql VOLATILE STRICT SECURITY INVOKER;

And you don't need PLpgSQL either, SQL will do.

Collectives™ on Stack Overflow

How to check if values exists in a table - postgres PLPGSQL

ERD

Problem summary

The stored procedure

The problem

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

ERD

Problem summary

The stored procedure

The problem

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related