Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux