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 CAFj8pRCx9NA1QrY+u0DDyQSz_Ogifp3A5FdBF2S7izmU2euALw@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>)
Ответы 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

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


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
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type