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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: DUBILLOT Aurelien
Дата:
Сообщение: Questions about PostgreSQL Setup License
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: Questions about PostgreSQL Setup License