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;
