BUG #7943: plpgsql parsing bug
От | rikard@ngs.hr |
---|---|
Тема | BUG #7943: plpgsql parsing bug |
Дата | |
Msg-id | E1UGDwW-0003XF-2l@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #7943: plpgsql parsing bug
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 7943 Logged by: Rikard Pavelic Email address: rikard@ngs.hr PostgreSQL version: 9.2.1 Operating system: Windows 7 Description: = ERROR: "_t1" is not a scalar variable LINE 12: for _i1, _i2, _t1, _t2, _t3, _b in select * from ( ^ create type s as ("URI" text, x int); create table t( i int, "some" s[]); create view v as select i::text as "URI", i, "some" from t; create table table_updated (i int, old v, new v); --this works select * from ( SELECT sq.i, sq.xx, cast(sq.old[xx] as s), = (select n from unnest(sq.new) n where n."URI" =3D sq.old[xx]."URI"), = sq.new[xx], = not exists(select o from unnest(sq.old) o where o."URI" =3D sq.new[xx]."UR= I") AND sq.new[xx]::text IS NOT NULL FROM = ( SELECT = t.i, = (t.old)."some" AS old, (t.new)."some" AS new, unnest((SELECT array_agg(x) FROM generate_series(1, CASE WHEN coalesce(array_upper((t.old)."some", 1), 0) > coalesce(array_upper((t.new)."some", 1),0) THEN array_upper((t.old)."some", 1) ELSE array_upper((t.new)."some", 1) END) x)) as xx = FROM "table_updated" t ) sq) sq --this throws an error create or replace function fast_select(out i int, out index int, out old s, out s, out new s, out is_new bool) returns setof record as $$ declare _old s[]; declare _new s[]; declare _i1 int; declare _i2 int; declare _t1 s; declare _t2 s; declare _t3 s; declare _b bool; begin for _i1, _i2, _t1, _t2, _t3, _b in select * from ( SELECT sq.i, sq.xx, cast(sq.old[xx] as s), = (select n from unnest(sq.new) n where n."URI" =3D sq.old[xx]."URI"), = sq.new[xx], = not exists(select o from unnest(sq.old) o where o."URI" =3D sq.new[xx]."UR= I") AND sq.new[xx]::text IS NOT NULL FROM = ( SELECT = t.i, = (t.old)."some" AS old, (t.new)."some" AS new, unnest((SELECT array_agg(x) FROM generate_series(1, CASE WHEN coalesce(array_upper((t.old)."some", 1), 0) > coalesce(array_upper((t.new)."some", 1),0) THEN array_upper((t.old)."some", 1) ELSE array_upper((t.new)."some", 1) END) x)) as xx = FROM "table_updated" t ) sq) sq loop i =3D _i1; index =3D _i2; old =3D _t1; changed =3D _t2; new =3D _t3; is_new =3D _b; return next; end loop; end $$ language plpgsql;
В списке pgsql-bugs по дате отправления: