Allow to test with snapshots

[S-Stanley]

Hello guys,

I have to update many tests of SQL functions, and it's a tough work because, when we add/update/delete new columns, we have to update all the previous assertions, which is kind of a lot of work.

I was wondering if it was possible to add snapshot feature, similar to what you can find on javascript testing library like jest toMatchSnapshot.

For example, now we are testing like this:

SELECT results_eq(
    $$
        SELECT
            column_name
        FROM
            table_name
    $$,
    $$
        VALUES(
            'x'
        ), (
            'y'
        ), (
            '42'
        )
    $$,
    'Assert that...'
)

And using snapshots it would be for example

SELECT snapshot_equal(
    $$
        SELECT
            column_name
        FROM
            table_name
    $$,
    'Assert that...'
)

[theory]

Where would it store the snapshot?

[ewie]

This sounds like the regression testing in Postgres: comparing the output of psql scripts to the stored expected output.

pgTAP could implement something similar using temporary tables and \copy for loading the snapshot data.