2

Lets say we have table post with some rows in it:

create table post(
  id serial primary key,
  title text not null,
  body text not null,
  created_at timestamp not null 
);

We can select a subset of those columns as a row type like this:

select row(id, title) as foo from post;

  foo::record
----------------
(1, "First Post")

We can serialize that to text easily:

select row(id, title)::text as foo from post;

    foo::text
------------------
'(1, "First Post")'

How can that text then be deserialized back into a row(int,text)?
Something like (pseudo-code):

select '(1,"First Post")'::row(int,text)  -- not valid
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Oliver Rice
  • 768
  • 8
  • 20

2 Answers2

2

The core of the problem is this: a ROW constructor like you have there produces an anonymous record (a pseudo-type), which preserves nested original values as original data types, but loses the meta information on names and data types.

Original values are still in there (not just as unknown or text). The ROW value is notably more than its text representation, the row literal (1, "First Post"), which is stripped of almost all type information. But Postgres demands the "ingredient list" up front to expand a record to individual fields (or a row to individual columns).

When dealing with well-known row types (named types from tables, views, subqueries, registered composite types etc.) this meta information is retrieved from catalog tables (pg_attribute at its core). To expand an anonymous record, you need to provide the information somehow.

The simple way is to cast to a named row/composite type - which also works for the row literal. Postgres fetches required meta information from said catalog tables. That's what Gordon's solution with CREATE TYPE does. Then you can expand the row with simple * syntax to get individual fields:

CREATE TYPE foo_type AS (id int, title text);

Extract fields after casting:

SELECT ('(1,"First Post")'::foo_type).*;       -- for row literal input
SELECT (ROW(id, title)::foo_type).* FROM post; -- for actual record input

But you are trying to ...

avoid creating a bunch of composites

If you just don't want to create composite types explicitly - any registered row type from a table or view is good for it:

CREATE VIEW foo_view AS SELECT id, title FROM post;
SELECT ('(1,"First Post")'::foo_view).*;

Even TEMPORARY objects would do:

CREATE TEMP VIEW foo_view AS ...

Still registers the row type, but only the current session can see it.

Finally, there are even ways without additional registered row type:

1. Use row type derived from underlying table(s)

SELECT foo.* FROM (SELECT id, title FROM post) foo;

Note the subquery instead of a ROW constructor. This allows Postgres to look up fields in the underlying table(s).

2. Use a dummy function returning record with a target definition list.

This is advanced stuff. Functions can return anonymous records. You must provide a column definition list to expand rows. Rarely useful. This is one of the rare cases. But Postgres does not allow record as function input parameter:

CREATE OR REPLACE FUNCTION f_expand_record(record)
  RETURNS record LANGUAGE sql AS
'SELECT $1';
ERROR:  SQL functions cannot have arguments of type record

We can resort to polymorphic input, which circumvents this limitation:

CREATE OR REPLACE FUNCTION f_expand_record(anyelement)
  RETURNS record LANGUAGE sql AS
'SELECT $1';

Now ROW input is possible:

SELECT * FROM f_expand_record(ROW(1,text 'First Post')) AS t(id int, title text);

But a row literal is not, as polymorphic functions demand typed input:

SELECT * FROM f_expand_record('(1,"First Post")') AS t(id int, title text);

db<>fiddle here

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • My (incorrect) use of row and record interchangeably confused things a bit. The main goal is to cast from a text representation of an anonymous record, back to an anonymous record. From your explanation, it seems like that isn't possible without first registering a concrete type via creating a composite/view/etc (please correct me if that isn't right). I hoped something like `select * from (select id, title from post) foo where foo = '(1,"First Post")'::foo` or a cte might temporarily register a foo type that could be used for casting, but no such luck! Great response Thanks! – Oliver Rice Jan 04 '20 at 15:21
  • @OliverRice: Sounds like you got everything right. Like the error message in this [additional fiddle](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=50e557fc191881741ad35e75c24ce2eb) informs (and you have probably seen yourself by now): `ERROR: input of anonymous composite types is not implemented`. This will likely *never* work, because the row *literal* does not contain any type information - which anonymous records still carry, as explained above. But check out the additional fiddle, I added a few more potentially useful bits. – Erwin Brandstetter Jan 06 '20 at 02:00
  • Hi. @ErwinBrandstetter `SELECT * FROM f_expand_record(ROW(1,'first post'::text)) AS t(id int, title text); `is same as `SELECT * FROM f_expand_record(ROW(1,text 'First Post')) AS t(id int, title text);` the **text** is important in here. However int I don't explicit mention int. Can you further explain or just drop a link about the **text** usage in this context. – jian Mar 19 '22 at 11:59
  • 1
    @Mark: See https://stackoverflow.com/a/13676871/939860 and https://stackoverflow.com/a/24771122/939860 – Erwin Brandstetter Mar 20 '22 at 07:38
1

I don't know if there is a way to create types on the fly. You can do this with an explicit type cast:

create type x AS (a int, b text);

select '(1,"First Post")'::x
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786