PostgreSQL – How to Return Nested Type in a Function

postgresql

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.

Best Answer

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;
Related Question