BUG #7943: plpgsql parsing bug

From: rikard(at)ngs(dot)hr
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #7943: plpgsql parsing bug
Date: 2013-03-14 19:36:00
Message-ID: E1UGDwW-0003XF-2l@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 7943
Logged by: Rikard Pavelic
Email address: rikard(at)ngs(dot)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;

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2013-03-14 23:40:41 Re: Questions about PostgreSQL Setup License
Previous Message Devrim GÜNDÜZ 2013-03-14 18:56:47 Re: BUG #7913: TO_CHAR Function & Turkish collate