Postgresql – How to generates VALUES literal expression using a query

postgresqlpsql

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.

Best Answer

Are you looking for this:

WITH foobar(foo, bar) AS (
  VALUES
  (1::integer,'a'::text),
  (2,'b'),
  (3,'c'),
  (4,'d')
)
SELECT format('(%s, %L),', foo, bar) FROM foobar;

  format   
-----------
 (1, 'a'),
 (2, 'b'),
 (3, 'c'),
 (4, 'd'),
(4 rows)
Related Question