Invalid input syntax for type integer: "(2,2)" with composite data type while executing function

Question

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:

• Format specifier for integer variables in format() for EXECUTE?

For a function with composite type:

CREATE OR REPLACE FUNCTION public.get_parent_ltree
            (_parent_id int, 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$;

The effective query executed:

select l_tree from parent_tree where parent_id = 1;

Executing the function:

select get_parent_ltree(1,'parent_tree');
select get_parent_ltree(1,'public.parent_tree');

I get this error:

ERROR:  invalid input syntax for type integer: "(2,2)"  
CONTEXT:  PL/pgSQL function get_parent_ltree(integer,regclass) line 3 at EXECUTE

Context of line 3:

Howlend

Answer 1

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.

Forgot

I know why I stay away from row types ;) – Pelota

@a_horse_with_no_name: I don't disagree. I can handle them after a path of trial and tribulation. But I'd rather stick to simple types whenever possible. And that's my advice for beginners in any case. – Forgot

Thanks for the clarification. I had no idea about this anomaly and feel ambivalent about it. I'm glad this behavior is documented, still I can't help but get the feeling that it's a masked shortcoming. And the error message a user receives in such a case is strange (to put it mildly). – Toplofty

@klin: Yeah, this is a dark back alley in PL/pgSQL town. What adds to the confusion is that a set-returning function with RETURN QUERY takes kind of the opposite approach: db<>fiddle here There are reasons for everything, but the result is pretty confusing. – Forgot

Or even: db<>fiddle here – Forgot

Answer 2

It looks like a Postgres bug but Erwin clarifies the issue in the adjacent answer. One of the natural workarounds is 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$;

Toplofty

It seems to be related to PL/pgSQL. A return query select ... without dynamic SQL results in the same error. A language sql function (obviously without dynamic SQL) works however. – Pelota

@a_horse_with_no_name: I think my answer can explain it. – Forgot