Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type
Дата
Msg-id CAFj8pRCAkBHqzeswc--UV8s=BSgafB2PQH5iDw08VZWbM8dE+Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type  (Pavel Stehule <pavel.stehule@gmail.com>)
Список pgsql-general


út 10. 8. 2021 v 21:25 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:

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.

There are a lot of use cases for static composite types. Everywhere on the interface.




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


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

В списке pgsql-general по дате отправления:

Предыдущее
От: Pavel Stehule
Дата:
Сообщение: Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type
Следующее
От: Bryn Llewellyn
Дата:
Сообщение: Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type