During development and testing VALUES literal expressions are useful because they enable you to store data definition in your SQL query.
WITH foobar(foo, bar) AS ( VALUES (1::integer,'a'::text), (2,'b'), (3,'c'), (4,'d') ) SELECT * FROM foobar;
However this can become tedious when trying implement really wide or big table. And it's even more frustrating when this could have been generated from an existing table.
So I there a way to output rows in format easily copy/paste-able as a literal VALUES expression?
The closest I could come by is to output row as record
(please note the meta because this SQL actually try to reverse engineer literal VALUES back from a given literal VALUES).
WITH foobar(foo, bar) AS ( VALUES (1::integer,'a'::text), (2,'b'), (3,'c'), (4,'d') ) SELECT foobar::record FROM foobar;
Here is the psql output:
foobar -------- (1,a) (2,b) (3,c) (4,d) (4 rows)
However this need extra editing to rightfully quote,type and escape content so should i look for a formatting output trick or an SQL trick?
EDIT: The current top answer is already very good but ideally I would like to know if there is generic way to generate a value expression that can adapt to whatever record type you can throw at (even if it is a composite row with a random numbers of columns).
Let say this could be wrapped in as function like:
row_to_values(IN myrowtype record, OUT myrowtype_as_literal_values text)
At this stage one might wonder "Why not use row_to_json() ?" and yes this could be a valid alternative but we are drifting away from the initial use case I had in mind (quickly generate SQL for testing/occasional purpose) although using json should perform well and is now widely available among PostgreSQL releases.
Even better would be an aggregate function that directly output a clean text definition that specify types on first VALUE and add columns name as alias (but at this stage it's almost a feature proposal and here is not the right place and I'm nobody to ask for such a thing).
Unfortunately I guess this is currently an out of reach goal because as far as my understanding goes it would require a low level access to dynamic metadata about query record type. It's clearly doable for an existing table by querying pg_catalog.pg_attribute but I don't see how to achieve that with dynamic queries.
So if someone come with a nice trick I haven't though off, that would be impressive, otherwise the less generic answer clearly deserve an acceptance.