SQL: Printing from an UPDATE command

Question

Lets say that I have a simple SQL script:

UPDATE my_table
SET data = new_data
WHERE my_table.data IS NULL;

I would like to print which id's that where updated in the UPDATE command. So something like this functionality-wise:

UPDATE my_table
SET data = new_data, RAISE NOTICE 'Updated %', my_table.id
WHERE my_table.data IS NULL;

Is this possible? I am using PostgreSQL.

You want to *return* the IDs, not print them. Printing means send a text message to the client. — Panagiotis Kanavos, May 26 '16 at 09:18
No, I want to print them as I am debugging my code - in the same way as I normally do with RAISE NOTICE from my script. — Arenlind, May 26 '16 at 11:02

score 2 · Accepted Answer · edited May 23 '17 at 12:07

Ok, I came up with something like this.

DO language plpgsql $$
DECLARE _id bigint;
BEGIN
    FOR _id IN
    WITH up AS (
      UPDATE my_table
        SET data = new_data
        WHERE data IS NULL
        RETURNING id)
    SELECT id from up
    LOOP
      RAISE NOTICE 'Updated %', _id;
    END LOOP;
END
$$;

It is a bit clumsy, but should work for you.

At first, RAISE NOTICE has to be wrapped like explained here. I found no way how to store multiple rows (...RETURNING id) into variables, which might not be possible with current versions. That's why I let a select follow the update, which itself feeds the RAISE you require.

score 1 · Answer 2 · answered May 26 '16 at 09:21

You can return use the RETURNING clause of the UPDATE statement to return values from each updated row. From the documentation's example:

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
  WHERE city = 'San Francisco' AND date = '2003-07-03'
  RETURNING temp_lo, temp_hi, prcp;

This will return the updated temperatures.

In your case you could try:

UPDATE my_table
 SET data = new_data
 WHERE my_table.data IS NULL
 RETURNING id;

This does not answer my question: How do I print the id's that were updated? — Arenlind, May 26 '16 at 11:01

score 0 · Answer 3 · answered May 26 '16 at 09:24

0

If you want to return the updated rows ID then Try like this,

For Update:

 UPDATE my_table SET col2 = val2 WHERE col1 = val1 RETURNING ID;

For Insert:

INSERT INTO my_table (col2) values ( val2 ) RETURNING ID;

answered May 26 '16 at 09:24

StackUser

5,370
2
24
44

This does not answer my question: How do I print the id's that were updated? – Arenlind May 26 '16 at 11:01

SQL: Printing from an UPDATE command

3 Answers3