CODEWITHSUNDEEP
sqlpostgresqlstored-proceduresplpgsql
stav bitanski
stav bitanski

Reputation: 23

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

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.

Upvotes: 0

Views: 2044

Answers (2)

Frank Heikens
Frank Heikens

Reputation: 126991

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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)

Upvotes: 1

Related Questions