How to generates VALUES literal expression using a query?

Question

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.

Answer 1

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)

Answer 2

Ok so based on @laurenz-albe solution and some thinkering about JSON option I built a query that solve the question in a more generic way but is ugly as hell.

I guess someone more talented could wrap it into a PL/PGSQL function that take a query::text as input. Maybe relying on JSON function not being the best idea because I'm not sure how it will handle sorting in all situations.

So here comes the beast (tested on PostgreSQL 10):

--Convert any query inserted into this CTE as a nice VALUES list + definition
WITH query_to_values AS (
  SELECT * FROM
  (VALUES
  (1::integer,'a'::text),
  (2,'b'),
  (3,'c'),
  (4,'d')) foobar(foo,bar)
),

--Step 1 convert row to json, number lines and identify first and last line
step1 AS (
  SELECT
    row_number() OVER (),
    row_number() OVER () = 1 is_first,
    row_number() OVER () = count(*) OVER () is_last, 
    row_to_json(query_to_values)
  FROM query_to_values
),

--Step 2 Unnest JSON, keep key metadata and format value as literal
step2 AS (
  SELECT
    *,
    (json_each_text(row_to_json)).key,
    format(
      '%1$L%2$s'
      ,(json_each_text(row_to_json)).value
      ,CASE WHEN is_first THEN '::text' ELSE ''END
    ) column_content
  FROM step1
),

--Step3 First column concat values fields and add a CAST (Default to ::text) on first line, build definition on first line
step3 AS (
  SELECT
    row_number,
    concat(
      CASE WHEN is_first THEN E'(VALUES\n' END,
      '(',
      string_agg(column_content,','),
      ')',
      CASE WHEN NOT is_last THEN ',' ELSE E'\n)' END
    ),
    'query_to_values ('||string_agg(key,',') FILTER (WHERE is_first)||')' definition
  FROM step2
  GROUP BY row_number, is_first, is_last
  ORDER BY row_number
),

--Step 4 Aggregate all as nice values list with newline and a definition (to paste wherever fit if needed)
step4 AS (
  SELECT
    min(definition) definition,
    string_agg(concat,E'\n') values_list
  FROM step3
)

--You can display intermediate steps by flipping step number
SELECT * FROM step4

---

As an example this is how you could use is to generate VALUES list from a real table

WITH query_to_values AS (
  --This a complex table with million+ lines so i'll keep first 10
  SELECT * FROM insee_rgp.sx_base_ic WHERE id_zone='010010000' LIMIT 10
)

--Assume the rest of the script is here, I won't duplicate from first codeblock ;)

This will generate this value list with correct escaping

(VALUES
('2008'::text,'ia_iris_2013'::text,'010010000'::text,'L''Abergement-Clémenciat'::text,'00'::text,'5'::text,'C_ACT1564'::text,'386.234279'::text),
('2009','ia_iris_2014','010010000','L''Abergement-Clémenciat','00','5','C_ACT1564','379.374359'),
('2010','ia_iris','010010000','L''Abergement-Clémenciat','00','5','C_ACT1564','377.928205'),
('2011','ia_iris_2013','010010000','L''Abergement-Clémenciat','00','5','C_ACT1564','376'),
('2012','ia_iris_2013','010010000','L''Abergement-Clémenciat (commune non irisée)','00','5','C_ACT1564','374.553846153846'),
('2013','ia_iris_2013','010010000','L''Abergement-Clémenciat (commune non irisée)','00','5','C_ACT1564','369.733333333333'),
('2014','ia_iris_2014','010010000','L''Abergement-Clémenciat (commune non irisée)','0','5','C_ACT1564','430'),
('2015','ia_iris','010010000','L''Abergement-Clémenciat (commune non irisée)','0','5','C_ACT1564','430'),
('2016','ia_iris','010010000','L''Abergement-Clémenciat (commune non irisée)','0','5','C_ACT1564','430'),
('2008','ia_iris_2013','010010000','L''Abergement-Clémenciat','00','5','C_ACT1564_CS1','15.605425')
)
--Copyright: INSEE Paris France Census

---

The manual tasks remaining are :

1. To stick the definition wherever needed because syntax depend whether it's used in WITH or FROM clause.

WITH query_to_values (foo,bar) AS (VALUES ...)

or

SELECT * FROM 
(VALUES ...) AS query_to_values (foo, bar)

2. To manually edit the CAST to the appropriate data-types (in my example some columns are integer or double precision)

('2008'::integer,'ia_iris_2013'::text,'010010000'::text,'L''Abergement-Clémenciat'::text,'00'::text,'5'::integer,'C_ACT1564'::text,'386.234279'::double precision),

Feel free to reuse as an inspiration for a better implementation as this code is pretty horrible!

Feel like could be a useful snippet when writing pg_tap tests.