0

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;

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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