Deserialize text to record

Question

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

Answer 1

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