How to create a function with a condition on a column array in pl/pgsql?-CodePudding

I want to create an upvote function that will increment a number only if the uid parameter is not contained in the already_voted_uid array and also add that uid into this array if the conditions are met. Here is a pseudocode of my attempt:

create function upvote (cid text, uid uuid) 
returns void as $$
begin
  update submissions
  set number_of_votes = number_of_votes   1
  where cid = cid
  and already_voted_uids does not includes uid <-- this is pseudo code
  set already_voted_uids = already_voted_uid.push(uid) <-- this is pseudo code
end;
$$ language plpgsql security definer;

already_voted_uids is an array of uids

CodePudding user response：

You can use <> ALL()

create function upvote (cid_to_upvote text, uid text) 
returns void as $$
begin
  update painting_submissions
    set number_of_votes = number_of_votes   1, 
        already_voted_uid = already_voted_uid||uid
  where cid = cid_to_upvote
    and uid <> all(already_voted_uid);
end;
$$ language plpgsql security definer;

CodePudding user response：

So I've managed to find a working solution:

create function upvote (cid_to_upvote text, uid text) 
returns void as $$
begin
  update painting_submissions
  set number_of_votes = number_of_votes   1, already_voted_uid = array_append(already_voted_uid, uid)
  where cid = cid_to_upvote
  and  NOT EXISTS (
   SELECT 1
   FROM   unnest(already_voted_uid) AS voted
   WHERE  voted LIKE uid
   );
end;
$$ language plpgsql security definer;