postgres select into another table and return new tables results as json

Question

I am trying to copy data from one table to another that fulfils a criteria.

If I run my function it only returns one row of data instead of the 3 it should.

However if I run the INSERT command separately it correctly copies all the data to the new table (3 rows). When I run the RETURN QUERY SELECT separately it correctly returns 3 rows.

Is this a race condition please?

Please note _ref_date is ignored and I'm using hardcoded dates to debug.

CREATE OR REPLACE FUNCTION new_table(
 _ref_date timestamp DEFAULT CURRENT_DATE)
 RETURNS table (j json)
AS $$
 BEGIN
 
 INSERT INTO new_table
 (
 LASTUPDATED_BY,
 LASTUPDATED_ON,
 QTY
 )
 SELECT 
 updated_by,
 lastupdated_on,
 qty
 FROM
 old_table
 WHERE
 lastupdated_on >= '2023-05-24 00:00:00+00'
 AND
 lastupdated_on < '2023-05-27 00:00:00+00';
 
RETURN QUERY SELECT row_to_json(bod) FROM (
 SELECT * FROM new_table) bod;
END;
$$
LANGUAGE plpgsql;

Answer 1

I think this may have been some caching issue in pgadmin as having dropped the function and recreated it I am getting the right results.