Return to Answer

added 1745 characters in body

Source Link

edited Mar 24, 2018 at 18:20

23.9k
11
50
89

As @ypercube has pointed out, when you executes:

SELECT tbl FROM tbl;

you get this result:


| tbl           |
| :------------ |
| (1,"Title 1") |
| (2,"Title 2") |
| (3,"Title 3") |

same output as using:

SELECT ROW(1, 'Title 1');


| row           |
| :------------ |
| (1,"Title 1") |

It is a (named) composite type. (Have a look at docs about TYPE)

By default, the value created by a ROW expression is of an anonymous record type. If necessary, it can be cast to a named composite type — either the row type of a table, or a composite type created with CREATE TYPE AS

Having a look at row_to_json() function:

+------------------------+----------------------------------------------------+---------------------------+---------------------+
| row_to_json(record [,  | Returns the row as a JSON object.                  | row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
|          pretty_bool]) | Line feeds will be added between level-1 elements  |                           |                     |
|                        | if pretty_bool is true.                            |                           |                     |
+------------------------+----------------------------------------------------+---------------------------+---------------------+

Uses a row as example: row_to_json(row(1,'foo'))

As @ypercube has pointed out, when you executes:

SELECT tbl FROM tbl;

you get this result:


| tbl           |
| :------------ |
| (1,"Title 1") |
| (2,"Title 2") |
| (3,"Title 3") |

same output as using:

SELECT ROW(1, 'Title 1');


| row           |
| :------------ |
| (1,"Title 1") |

It is a (named) composite type. (Have a look at docs about TYPE)

By default, the value created by a ROW expression is of an anonymous record type. If necessary, it can be cast to a named composite type — either the row type of a table, or a composite type created with CREATE TYPE AS

Having a look at row_to_json() function:

+------------------------+----------------------------------------------------+---------------------------+---------------------+
| row_to_json(record [,  | Returns the row as a JSON object.                  | row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
|          pretty_bool]) | Line feeds will be added between level-1 elements  |                           |                     |
|                        | if pretty_bool is true.                            |                           |                     |
+------------------------+----------------------------------------------------+---------------------------+---------------------+

Uses a row as example: row_to_json(row(1,'foo'))

Source Link

created Mar 24, 2018 at 16:58

McNets

23.9k
11
50
89

Given next example:

CREATE TABLE tbl (id int, title text);
     
INSERT INTO tbl VALUES
(1, 'Title 1'),
(2, 'Title 2'),
(3, 'Title 3');

Does this mean a sub-query is the same as a record and a row value/composite value?

Let me select some records from tbl:

SELECT id, title FROM tbl;

id | title  
-: | :------
 1 | Title 1
 2 | Title 2
 3 | Title 3

Now using a subquery:

SELECT * FROM (SELECT id, title FROM tbl) t;

id | title  
-: | :------
 1 | Title 1
 2 | Title 2
 3 | Title 3

As you can see both queries return same rows, then you can use both queries to generate a JSON result.

SELECT row_to_json(x) 
FROM   (SELECT id, title FROM tbl) x;

| row_to_json                |
| :------------------------- |
| {"id":1,"title":"Title 1"} |
| {"id":2,"title":"Title 2"} |
| {"id":3,"title":"Title 3"} |

dbfiddle here