begin;
create type public.ltree as (a int, b int);
create  table public.parent_tree(parent_id int,l_tree ltree);
insert into public.parent_tree values(1,(2,2)),(2,(1,2)),(3, (1,28));
commit;

Trying to replicate the solution in this answer:

• 

  CodePudding user response：

  The output parameter _l_tree is a "row variable". (A composite type is treated as row variable.) SELECT INTO assigns fields of a row variable one-by-one. The manual:

  The optional target is a record variable, a row variable, or a comma-separated list of simple variables and record/row fields, [...]

  So, currently (pg 14), a row or record variables must stand alone as target. Or as the according Postgres error message would put it:

  ERROR:  record variable cannot be part of multiple-item INTO list
  

  This works:

  CREATE OR REPLACE FUNCTION public.get_parent_ltree (IN  _parent_id int
                                                    , IN  _tbl_name  regclass
                                                    , OUT _l_tree    ltree)
    LANGUAGE plpgsql AS
  $func$
  BEGIN
     EXECUTE format('SELECT (l_tree).* FROM %s WHERE parent_id = $1', _tbl_name)
     INTO  _l_tree
     USING _parent_id;
  END
  $func$;
  

  Or this:

  CREATE OR REPLACE FUNCTION public.get_parent_ltree2 (IN  _parent_id int
                                                     , IN  _tbl_name  regclass
                                                     , OUT _l_tree    ltree)
    LANGUAGE plpgsql AS
  $func$
  BEGIN
     EXECUTE format('SELECT (l_tree).a, (l_tree).b FROM %s WHERE parent_id = $1', _tbl_name)
     INTO  _l_tree.a, _l_tree.b
     USING _parent_id;
  END
  $func$;
  

  db<>fiddle here

  I agree that this is rather tricky. One might expect that a composite field is treated as a single field (like a simple type). But that's currently not so in PL/pgSQL assignments.

  A related quote from the manual about composite types:

  A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types. PostgreSQL allows composite types to be used in many of the same ways that simple types can be used.

  Bold emphasis mine.
  Many. Not all.

  Related:

  • Use of custom return types in a FOR loop in plpgsql
  • PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"

  ---

  Aside: Consider the additional module ltree instead of "growing your own". And if you continue working with your own composite type, consider a different name to avoid confusion / conflict with that module.

  CodePudding user response：

  Unfortunately, everything indicates that this is a Postgres bug. Please consider reporting this issue on Postgres bugs list.

  The only viable workaround seems to be to use an auxiliary text variable in the way like this:

  create or replace function get_parent_ltree(_parent_id int, tbl_name regclass)
  returns ltree language plpgsql as
  $func$
  declare
      rslt text;
  begin
      execute format('select l_tree from %s where parent_id = $1', tbl_name)
      into rslt
      using _parent_id;
      return rslt::ltree;
  end
  $func$;