Обсуждение: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

https://stackoverflow.com/questions/41843016/how-do-i-pass-a-user-defined-type-variable-to-a-function-as-a-parameter

Question 1.

-----------

Where, in the PL/pgSQL doc, does it state that "select col into var" when col is a user-defined type doesn't work—and where is the viable approach shown?

Question 2.

-----------

If I can easily re-write a failing approach by hand (once I know that I must) so that it works, why cannot the PL/pgSQL compiler do this under the covers?

The problem that I report here seems to be known and seems, too, to astonish and annoy users. It's a bare "computer says No". It's hard to find anything of ultimate use with Google search (either constrained to the PG doc or unconstrained). Here's an example on stackoverflow: 

https://stackoverflow.com/questions/41843016/how-do-i-pass-a-user-defined-type-variable-to-a-function-as-a-parameter

However, it does give the _clue_ to the workaround.

Here's an illustration of the issue, starting with what works fine. I tested in using PG 13.3.

create type rect as (h int, w int);
create table t1(k int primary key, r1 rect not null);
insert into t1(k, r1) values(1, (10, 20));

do $body$
declare
  r rect;
begin
  r := (
    select r1
    from t1
    where k = 1);
  raise info '%', r::text;
end;
$body$;

The "raise info" shows what you'd expect.

This re-write fails. It simply uses the approach that anybody who hasn't yet been bitten by this would expect to work.

do $body$
declare
  r rect;
begin
  select r1 -- line 5
  into r
  from t1
  where k = 1;
end;
$body$;

This is the error:

22P02: invalid input syntax for type integer: "(10,20)" ... at line 5

With "VERBOSITY" set to "verbose", there's not hint to tell you what the problem is and how to work around it.

Question 1.

-----------

Where, in the PL/pgSQL doc, does it state that "select col into var" when col is a user-defined type doesn't work—and where is the viable approach shown?

Question 2.

-----------

If I can easily re-write a failing approach by hand (once I know that I must) so that it works, why cannot the PL/pgSQL compiler do this under the covers?

Pavel Stehule <pavel.stehule@gmail.com> writes:
> Some errors like this, but not this can be detected by plpgsql_check
> https://github.com/okbob/plpgsql_check - probably the heuristic for type
> check is not complete.

STRICTMULTIASSIGNMENT would detect most cases of this, except that
the condition is checked too late.  We'd need to count the fields
*before* trying to assign values, not after.

In the meantime, it does seem like the docs could be more explicit
about this, and perhaps give an example showing the (x).* solution.

                        regards, tom lane

It now seems to me to be odd, in the light of the explanations for why the naïve (PL/SQL-style) syntax doesn't work in PL/pgSQL, that assigning a scalar subquery to a variable of the composite type in question _does_ work! But don't take that as a question. I'm going to regard this as "case closed".

pavel.stehule@gmail.com wrote:

tgl@sss.pgh.pa.us wrote:

pavel.stehule@gmail.com wrote:

Some errors like this, but not this can be detected by plpgsql_check
https://github.com/okbob/plpgsql_check

probably the heuristic for type check is not complete.

STRICTMULTIASSIGNMENT would detect most cases of this, except that the condition is checked too late.  We'd need to count the fields *before* trying to assign values, not after.

In the meantime, it does seem like the docs could be more explicit about this, and perhaps give an example showing the (x).* solution.

Yes, a more detailed explanation of this behavior can be nice.  There can be an example of value unnesting, but I think so for this case, there should be mainly an example of ANSI assign syntax.

var := (SELECT x FROM ..)

This syntax has advantages so is not amigonuous for this case, and explicit unnesting is not necessary (and it is not possible). Moreover, this is ANSI SQL syntax.

Consider this example:

create type type1 as (a1 int, a2 int);
create table tab1(k int primary key, b boolean not null, t type1 not null);
insert into tab1(k, b, t) values(1, true, (10, 20));
select b::text, t::text from tab1 where k = 1;

It seems to be perfectly plausible—and so it seems equally plausible that you'd want to do it using PL/pgSQL. Each of these two alternatives, inspired by the advice that I got in this thread, works and produces the expected output:

do $body$
declare
  r record;
begin
  select ((b, t)).*
  into r
  from tab1
  where k = 1;
  raise info 'Alt 1: % | %', r.f1::text, r.f2::text;

  r := (
    select (b, t)
    from tab1
    where k = 1);
  raise info 'Alt 2: % | %', r.f1::text, r.f2::text;
end;
$body$;

It feels a smidge uncomfortable because I'm forced to use reference by field position (f1, f2) rather than by field name (b, t). But reference by position is commonplace in PostgreSQL (for example, in the PREPARE statement). So I'spose that I have to learn to like it.

Do you (all) think that the "Alt 1" and "Alt 2" that I show are the best way to implement the requirement? I prefer "Alt 2" because it doesn't have the clutter (and the burden for understanding and readability) of the extra parentheses and the ".*".

I can get the reference by field name that I prefer with a schema-level type:

create type tmp as (b text, t type1);

and by declaring "r" with this data type. But this is a greater discomfort than using the dynamically shaped "record" because it needs you to create a dedicated schema-level type for every new SELCT list that you come need.

I can get the reference by field name that I prefer with a schema-level type:

create type tmp as (b text, t type1);

and by declaring "r" with this data type. But this is a greater discomfort than using the dynamically shaped "record" because it needs you to create a dedicated schema-level type for every new SELCT list that you come need.

When It is possible I use a record type - some years ago, the work with this type was a little bit slower, but not now. The work with this type is little bit safer - because it gets real labels. Values with declared composite types uses positional assignment, that is not too safe.

create table foo(a int, b int);

create type footype as (a int, b int);

insert into foo values(10, 200);

postgres=# do $$
declare f footype;
r record;
begin
  select b, a from foo into f;
  select b, a from foo into r;
  raise notice 'f.a: %, f.b: %', f.a, f.b;
  raise notice 'r.a: %, r.b: %', r.a, r.b;
end;
$$;
NOTICE:  f.a: 200, f.b: 10
NOTICE:  r.a: 10, r.b: 200
DO
postgres=# select * from foo;
┌────┬─────┐
│ a  │  b  │
╞════╪═════╡
│ 10 │ 200 │
└────┴─────┘
(1 row)

But sometimes explicit type is necessary - when you want to return composite value and when you want to work with composite outside function, or when you want to serialize, or deserialize composite value to/from json.

When you work with composite values, is good to enable warnings

https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS

Regards

Pavel

Pavel Stehule <pavel.stehule@gmail.com> writes:
> Some errors like this, but not this can be detected by plpgsql_check
> https://github.com/okbob/plpgsql_check - probably the heuristic for type
> check is not complete.

STRICTMULTIASSIGNMENT would detect most cases of this, except that
the condition is checked too late.  We'd need to count the fields
*before* trying to assign values, not after.

In the meantime, it does seem like the docs could be more explicit
about this, and perhaps give an example showing the (x).* solution.

                        regards, tom lane

I can get the reference by field name that I prefer with a schema-level type:

create type tmp as (b text, t type1);

and by declaring "r" with this data type. But this is a greater discomfort than using the dynamically shaped "record" because it needs you to create a dedicated schema-level type for every new SELCT list that you come need.

When It is possible I use a record type - some years ago, the work with this type was a little bit slower, but not now. The work with this type is little bit safer - because it gets real labels. Values with declared composite types uses positional assignment, that is not too safe.

create table foo(a int, b int);

create type footype as (a int, b int);

insert into foo values(10, 200);

postgres=# do $$
declare f footype;
r record;
begin
  select b, a from foo into f;
  select b, a from foo into r;
  raise notice 'f.a: %, f.b: %', f.a, f.b;
  raise notice 'r.a: %, r.b: %', r.a, r.b;
end;
$$;
NOTICE:  f.a: 200, f.b: 10
NOTICE:  r.a: 10, r.b: 200
DO
postgres=# select * from foo;
┌────┬─────┐
│ a  │  b  │
╞════╪═════╡
│ 10 │ 200 │
└────┴─────┘
(1 row)

But sometimes explicit type is necessary - when you want to return composite value and when you want to work with composite outside function, or when you want to serialize, or deserialize composite value to/from json.

When you work with composite values, is good to enable warnings

https://www.postgresql.org/docs/current/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS

static composite types can be good when you use dynamic SQL. The plpgsql_check cannot derive output composite type from dynamic SQL.  And it can stop checking. When you use static composite type, then the check can continue.

Regards

Pavel

Regards

Pavel

r := (select (b, t)::type1 -- it is composite with labels again

Thanks, but I don't understand your "r := (select (b, t)::type1 -- it is composite with labels again". I tried this:

create procedure p(i in int)
  language plpgsql
as $body$
declare
  r record;
begin
  case i
    when 1 then
      select (b, t)::type1
      into r
      from tab1
      where k = 1;

    when 2 then
      r := (
        select (b, t)::type1
        from tab1
        where k = 1);

    else null;
  end case;
end;
$body$;

call p(3);
call p(2);
call p(1);

My idea with using a procedure and choosing which code path is followed at run-time is to distinguish between compile-time errors (there are none here) and run-time errors. Of course, "call p(3)" finishes with no error.

But both the other calls cause the same error:

42846: cannot cast type record to type1

But you say that this should work!