RE: Ways to "serialize" result set for later use?

From: Adam Brusselback <adambrusselback(at)gmail(dot)com>
Sent: Saturday, April 10, 2021 9:06 PM
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Ways to "serialize" result set for later use?

Hey there everyone,

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<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
.
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.