0

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.

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.