Postgresql how to return nested type in a function

Question

I'm trying to return nested type with a function, but always end up with some error. With example below I get:

select mytest();
ERROR:  malformed record literal: "test3"
DETAIL:  Missing left parenthesis.

create type nestedtype as (test1 text);
create type maintype as (test2 text, other nestedtype);

CREATE OR REPLACE FUNCTION mytest() returns maintype as $$
  SELECT 'test' AS test2, 
  ((select 'test3' as test1)::nestedtype) AS other
$$ LANGUAGE sql STABLE;

What am I doing wrong? How can I return nested type used together with aliases?

In the end I need to return multiple fields from nested type, so it's not possible just switch to text, just in case such suggestion would come up.

Answer 1

To create an instance of a type, you need a row constructor

So to create a value of nestedtype you need row('test1')::nestedtype. To create a value of maintype you essentially do the same, row(...)::maintype but for the second value you need again pass a proper row type:

row('test2 value', row('test1 value')::nestedtype)::maintype

This expression can be used in your function:

CREATE OR REPLACE FUNCTION mytest() 
  returns maintype 
as 
$$
  select row('test2 value', row('test1 value')::nestedtype)::maintype;
$$ 
LANGUAGE sql STABLE;