The following bug has been logged on the website:
Bug reference: 7943
Logged by: Rikard Pavelic
Email address: rik...@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" = sq.old[xx]."URI"),
sq.new[xx],
not exists(select o from unnest(sq.old) o where o."URI" =
sq.new[xx]."URI")
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" = sq.old[xx]."URI"),
sq.new[xx],
not exists(select o from unnest(sq.old) o where o."URI" =
sq.new[xx]."URI")
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 = _i1;
index = _i2;
old = _t1;
changed = _t2;
new = _t3;
is_new = _b;
return next;
end loop;
end
$$ language plpgsql;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
