RE: Ways to "serialize" result set for later use? - Mailing list pgsql-general

I am going through the process of writing my first pgtap tests for my database, and I wanted to get some feedback on if my solution seems fine, is just dumb, or could be acomplished much easier another way.

So my main problem I was trying to work around, was my tests are written in functions and called using runtests(), so using psql and \copy to save test data to the filesystem isn't really part of the workflow, but I still needed a way to have my "expected" query resultset passed into results_eq [https://pgtap.org/documentation.html#results_eq] easily within a function body.

I originally manually dumped some "known good" data from a query to csv, and built some SELECT ... FROM VALUES (...) statements by hand to do this. That obviously sucks.

…

So I really just wanted to see if there is a better way to go about what i'm trying to do, does Postgres already support something similar I can harness instead of this hack? Or is this really an alright way to go?

This seems more like an application question, but I'll throw something out for you to consider…

IMO, you're trying to put pgtap into an area it wasn't really made for. If you can make it do what you want, good for you, but I wouldn't try that. Pgtap is great for things like:

• Does my DB/Schema/Tables/Views/Functions/… exist, have the correct owner, etc.
• Does each table have the right columns, defaults, constraints, etc.
• Testing of simple functions is possible, but probably only really for "immutable" stuff, like I insert "x" I better always get "y" out of it.
• More DDL stuff like this that's important because you want to know if your "alter" script after an upgrade did the right thing.

Checking data (DML), if functions are doing the right things is something we do in our code unit tests.

Of course, TMTOWTDI, YMMV, etc. 😊

HTH,

Kevin

.