Execute update query with IN clause in postgres function

Question

I am trying to create a PostgreSQL function which will update a list of rows. So I want to take a list of text[] as an argument and use the IN clause to execute the query. Also, i want to get the number of rows updated as a returned value. This worked for me:

CREATE FUNCTION set_mail_isbeingused(list_of_mail_names text[]) RETURNS void AS $$
    BEGIN
        FOR i in 1 .. array_upper(list_of_mail_names,1)
            LOOP
                UPDATE mail_isbeingused set isbeingused = 'true' where mailname = list_of_mail_names[i];
            END LOOP;

    END;
    $$
LANGUAGE plpgsql;

But i want to execute it in one update query with IN clause.

CREATE FUNCTION set_mail_isbeingused(list_of_mail_names text[]) RETURNS void AS $$
    BEGIN
        UPDATE mail_isbeingused set isbeingused = 'true' where mailname in list_of_mail_names;
    END;
    $$
LANGUAGE plpgsql;

This is what I have been trying to do. Can somebody help me in fixing this?

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2015-12-17T12:58:11

You need to use the ANY operator with an array. You can't use IN

To obtain the number of rows update, use GET DIAGNOSTICS

CREATE FUNCTION set_mail_isbeingused(list_of_mail_names text[]) 
  RETURNS integer --<< you need to change the return type
AS $$
DECLARE
  l_rows integer;
BEGIN
  UPDATE mail_isbeingused 
     set isbeingused = true 
  where mailname = ANY (list_of_mail_names);
  GET DIAGNOSTICS l_rows = ROW_COUNT; --<< get the number of affected rows
  RETURN l_rows;
END;
$$
LANGUAGE plpgsql;

Boolean constants don't need to be put into single quotes true is a boolean value 'true' is a string constant.

Execute update query with IN clause in postgres function

1 Answers