Error after second read from CTE or TEMPORARY TABLE in Postgres

Question

I am trying to write a query where I select records by some condition, update one column of those records and then return them, all in one transaction.

I have tried two options. First with CTE:

BEGIN;
WITH unwatched_rows AS (
        SELECT * from my_table WHERE is_readed = false FOR UPDATE
)
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT;

In this case I get an error relation "unwatched_rows" does not exist. How I learned this is because i can't use CTE more than once. And as I understand it, CTE is not suitable for my situation

Second option with CREATE TEMPORARY TABLE:

BEGIN;
CREATE TEMPORARY TABLE unwatched_rows AS (
        SELECT * from my_table WHERE is_readed = false FOR UPDATE
);
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT;

I am running this query through DBeaver. For first time it works fine, but when I try to run this query again I get an error relation "unwatched_rows" already exists. I don't understand why this is happening. Shouldn't the temporary table be dropped right after the commit? What am I doing wrong and how to solve it?

Answer 1

You can certainly use a CTE (with ...) once. However you used it incorrectly. A CTE is essentially a view which exists only for the duration of the query where it is created. In query the semi-colon (;) after the update terminated the statement and thus the CTE itself goes out-of-scope. So for theselect statement it no longer exists. Postgres allows DML in a CTE so to correct your statement create a second CTE that does the update. So:

with unwatched_rows as (
        select id 
          from my_table 
         where is_readed = false for update
     )
   , upt as (
       update my_table 
          set is_readed = true 
        where id in (select id from unwatched_rows)
     ) 
select * 
  from unwatched_rows;

As far as wrapping into a function this is actually quite simple. It does not require a temp table not, for that matter, a CTE. It reduces to a single SQL statement:

create or replace function unwatched_rows()
  returns setof my_table.id%type 
 language sql 
as $$ 
    update my_table 
       set is_readed = true
     where not is_readed    -- same as: is_readed = false
    returning id;
$$;

See here for demo illustrating both.