Postgres: How to transform data in two cursors before comparison?

Question

I am replacing a legacy function get_data in our database which takes some entity_id and returns a refcursor.

I am writing a new function get_data_new which is using different data sources, but the outputs are expected to be the same as get_data for the same input.

I'd like to verify this with pgtap, and am doing so as follows within a test (with _expected and _actual being the names of the returned cursors):

SELECT schema.get_data('_expected', 123);
SELECT schema.get_data_new('_actual', 123);

SELECT results_eq(
    'FETCH ALL FROM _actual',
    'FETCH ALL FROM _expected',
    'get_data_new should return identical results to the legacy version'
);

This works as expected for other functions, but the query in get_data happens to return some json columns meaning that comparison expectedly fails with ERROR: could not identify an equality operator for type json.

I'd rather leave the legacy function untouched so refactoring to jsonb isn't possible. I'm imagining a workaround to be transforming the data before comparison, hypothetically with something like SELECT entity_id, json_column::jsonb FROM (FETCH ALL FROM _actual), but this specific attempt obviously isn't valid.

What would be a suggested approach here? Write a helper function to insert data from the cursors into a couple of temporary tables? I'm hoping there's a cleaner solution I haven't discovered.

Using postgres 11.14, pgtap11

Thanks @RichardHuxton. Have [written a wrapper function](https://stackoverflow.com/a/70870786/6772415) for the cursors to allow SELECTing from them so we can do arbitrary transformations of the data. — nicstella, Jan 26 '22 at 21:59

nicstella · Answer 1 · 2022-01-30T20:30:19.650

Have solved it by creating a function to loop over the cursor and return the results as a table. Unfortunately this isn't a generic solution - it only works for the cursors with specific data.

In this specific case, json_column can be implicitly converted to type jsonb so this is all that is needed. However, we can now SELECT * FROM cursor_to_table('_actual') meaning we can do whatever transformations we require on the result.

CREATE OR REPLACE FUNCTION cursor_to_table(_cursor refcursor)
RETURNS TABLE (entity_id bigint, json_column jsonb)
AS $func$
BEGIN
    LOOP
        FETCH _cursor INTO entity_id, json_column
        EXIT WHEN NOT FOUND;

        RETURN NEXT;
    END LOOP;

    RETURN;
END
$func$  LANGUAGE plpgsql;

SELECT results_eq(
    'SELECT * FROM cursor_to_table(''_actual'')',
    'SELECT * FROM cursor_to_table(''_expected'')',
    'get_data_new should return identical results to the legacy version'
);

Postgres: How to transform data in two cursors before comparison?

1 Answers1