3

I want to set a boolean on every entry in a table matching an array of row ids passed into a stored procedure. Help me crack the proper syntax to accomplish this? The following is as close as I've gotten.

CREATE PROCEDURE tester (id_list bigint[])
AS
$$
UPDATE some_table
SET touched = true
WHERE id IN (unnest(id_list));
$$ LANGUAGE sql;

CALL tester(ARRAY[12, 34]);

2 Answers 2

3

Use the ANY operator:

UPDATE some_table
  SET touched = true
WHERE id = ANY (id_list);
0

a_horse_with_no_name's ANY answer is tidier, but I also found out adding a SELECT in front of the unnest function also works:

CREATE PROCEDURE tester (id_list bigint[])
AS
$$
UPDATE some_table
SET touched = true
WHERE id IN (SELECT unnest(id_list));
$$ LANGUAGE sql;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.