What is a row constructor used for?

Question

In PostgreSQL, what is the ROW() function used for?

Specifically what is the difference between

SELECT ROW(t.f1, t.f2, 42) FROM t;

where f1 is of type int, f2 is of type text

and

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);

Iatry

Answer 1

You are confusing levels of abstraction. As other answers already point out, CREATE TYPE only registers a (composite / row) type in the system. While a ROW constructor actually returns a row.

A row type created with the ROW constructor does not preserve column names, which becomes evident when you try to convert the row to JSON.

While being at it, ROW is just a noise word most of the time. The manual:

The key word ROW is optional when there is more than one expression in the list.

Demo:

SELECT t                              AS r1, row_to_json(t)                           AS j1
     , ROW(1, 'x', numeric '42.1')    AS r2, row_to_json(ROW(1, 'x', numeric '42.1')) AS j2
     ,    (1, 'x', numeric '42.1')    AS r3, row_to_json(   (1, 'x', numeric '42.1')) AS j3
     ,    (1, 'x', '42.1')::myrowtype AS r4, row_to_json((1, 'x', '42.1')::myrowtype) AS j4
FROM  (SELECT 1, 'x', numeric '42.1') t;

db<>fiddle here
_{Old sqlfiddle}

r1 and j1 preserve original column names.
r2 and j2 do not.
r3 and j3 are the same; to demonstrate how ROW is just noise.
r4 and j4 carry the column names of the registered type.

You can cast the row (record) to a registered row type if number and data types of the elements match the row type - names of input fields are ignored.

• Return multiple columns of the same row as JSON array of objects

Nganngc

Answer 2

Row constructors can be used to build composite values to be stored in a composite-type table column, or to be passed to a function that accepts a composite parameter. Also, it is possible to compare two row values or test a row with IS NULL or IS NOT NULL.

4.2.13. Row Constructors

Example:

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);
CREATE TABLE mytable (ct myrowtype);
INSERT INTO mytable(ct) VALUES (CAST(ROW(11,'this is a test',2.5) AS myrowtype));

Loaning

Answer 3

You're asking about the difference between a value and a type.

It's about the same difference than between an object and a class in an OO language.

In the first case you're building a value which could be used in comparisons, in row writing, or to be passed to functions accepting composite parameters.

In the second case you're defining a type that can be used for example in a function or table definition.

Horace

At its Ingres heart, PG is OO. I think that's why we've been able to enjoy advanced SQL language features from early on (inherits, array, hstore, etc..), with JSON giving us a complete round trip of unpivot-->(re)pivot. And the more I use PG the more I find I'm doing functional programming (SQL generally, but PG especially). – Nanci

Answer 4

ROW(...) is not a function. It's SQL syntax, more like the ARRAY[...] constructor than like a function.

The ROW constructor is primarily used to form anonymous records. This can be useful when you need to keep a collection of fields together, but they don't correspond to an existing table type or composite data type.

These two are equivalent in PostgreSQL:

test=> SELECT t FROM (SELECT 1, 'x', NUMERIC '42.1') AS t;
     t      
------------
 (1,x,42.1)
(1 row)

test=> SELECT ROW(1, 'x', NUMERIC '42.1');
    row     
------------
 (1,x,42.1)
(1 row)

in that both create an anonymous record:

test=> SELECT pg_typeof(t) FROM (SELECT 1, 'x', NUMERIC '42.1') AS t;
 pg_typeof 
-----------
 record
(1 row)

test=> SELECT pg_typeof(ROW(1, 'x', NUMERIC '42.1'));
 pg_typeof 
-----------
 record
(1 row)

The records created by ROW can correspond to existing types, for when you're passing composite types to a function, e.g given:

CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);

you can create a myrowtype with:

test=> SELECT CAST(ROW(1, 'x', '42.1') AS myrowtype);
    row     
------------
 (1,x,42.1)
(1 row)

Siloa

Not completely equivalent, like you worked out yourself: https://mcmap.net/q/95340/-using-row_to_json-with-nested-joins – Nganngc

Could we reference a field from ROW(...) without CREATE TYPE ? – Tonality

@Charlie木匠 fields in ROW() seem to have names like f1, f2 etc. select (row(1,2,3)).f2 returns "2". But I still can't find a way to name fields without creating a named type. – Resultant

@Resultant In some contexts you can use a scalar subquery like (SELECT 1 AS f1, 'foo' AS f2) – Siloa

Thanks for the answer. I've exited database field for 5 years. :) – Tonality

@charlie-木匠 that's the holy grail, to dynamically cast w/out creating a type. Interestingly, TEMP TABLES will serve in the stead of a permanent type. A quick anonymous proc w/ a LOOP to create a temp table "tmp_new_type" is feasible (my project du jour AAMOF) and so long as the row-types are compatible, then we can cast between types like this: SELECT ( ( ROW( (r).* ) )::tmp_new_type ).* A potential downside to a temp table is stability: I've observed server ab-ends in older PG versions when doing this, dropping a temp table w/out a cascade arg. – Nanci

@Resultant There is a way to do this w/ intervening JSON functions, but you'll have to hard-code column names as a simple alias list when casting (similar to the CROSSTAB() function). Otherwise the pre-defined type can be a temp table (see prev. comment). – Nanci

@L.Rodgers you can CREATE TYPE pg_temp.foo ( x integer); ; you don't need the temp table. – Siloa

Answer 5

A quick aside, we can cast to-and-fro from one table type to another, with restrictions:

   /** create a TYPE **/
   CREATE  TYPE tyNewNames AS ( a int, b int , c int )  ;

    SELECT
     /** note: order, type & # of columns must match exactly**/
       ROW((rec).*)::tyNewNames    AS "rec newNames" -- f1,f2,f3 --> a,b,c
    , (ROW((rec).*)::tyNewNames).* -- expand the new names
    ,'<new vs. old>' AS "<new vs. old>"
    ,*
    FROM
        (
          SELECT
            /** inspecting rec: PG assigned stand-in names f1, f2, f3, etc... **/
             rec     /* a record*/ 
            ,(rec).* -- expanded fields f1, f2, f3
            FROM (
                    SELECT ( 1, 2, 3 ) AS rec -- an anon type record
                  ) cte0
        )cte1
    ;

+---------+-+-+-++------------+--------+--+--+--+
|rec                          |rec              |
|newnames |a|b|c|<new vs. old>|oldnames|f1|f2|f3|
+---------+-+-+-++------------+--------+--+--+--+
|(1,2,3)  |1|2|3|<new vs. old>|(1,2,3) |1 |2 |3 |
+---------+-+-+-++------------+--------+--+--+--+

A compressed example of this code might look like this:

SELECT ( (  ROW(   (rec).* ) )::tyNewNames ).* ;

db fiddle(uk) https://dbfiddle.uk/dlTxd8Y3

However as Erwin points out, with the exception of a few cases, ROW() is a noise word. It's also possible to simply recast the record on-the-fly with the new field names, and without the nested CTE's:

 /** create a TYPE **/
CREATE  TYPE tyNewNames AS ( a int, b int , c int )  ;
 
SELECT ( 1, 2, 3 )    -- an anon type record
,  (ROW( 1, 2, 3 )).* -- anon field names f1, f2, f3 with  ROW() wrapper
,  (   ( 1, 2, 3 )).* -- anon field names f1, f2, f3 w/out ROW() wrapper        

/** cast to new names OTF , f1,f2,f3 --> a,b,c **/
, ( ROW( 1 ,2 ,3 )::tyNewNames ).* --    row() wrapper
, (    ( 1 ,2 ,3 )::tyNewNames ).* -- no row() wrapper
;
+-------+--+--+--+---+--+---+--+-+-+--+-+-+
|row    |f1|f2|f3| f1|f2|f3 | a|b|c| a|b|c|
+-------+--+--+--+---+--+---+--+-+-+--+-+-+
|(1,2,3)|1 |2 |3 |  1| 2| 3 | 1|2|3| 1|2|3|
+-------+--+--+--+---+--+---+--+-+-+--+-+-+

https://dbfiddle.uk/OPJcuzs7

Nanci