Thread: BUG #7943: plpgsql parsing bug

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;

rikard@ngs.hr writes:
> 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 (
>                        ^

[ shrug ... ]  It's right: _t1 is not a scalar variable.  (It has
the composite type s.)

The reason this is problematic is that writing a composite variable as
the target of a FOR IN SELECT loop is defined as assigning all the
columns from the SELECT into fields of the composite variable.  Which
is not what you wanted to have happen there, even if we were to extend
the definition to allow a mixture of scalar and composite variables in
the target list.

I'd suggest declaring a record variable and using that as the target.

            regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 15.3.2013. 4:49, Tom Lane wrote:
>
> [ shrug ... ] It's right: _t1 is not a scalar variable. (It has
> the composite type s.)
>
> The reason this is problematic is that writing a composite variable as
> the target of a FOR IN SELECT loop is defined as assigning all the
> columns from the SELECT into fields of the composite variable. Which
> is not what you wanted to have happen there, even if we were to extend
> the definition to allow a mixture of scalar and composite variables in
> the target list.
>
> I'd suggest declaring a record variable and using that as the target.
>
> regards, tom lane
>

I changed my approach, so I can live with current behavior, just wanted to report a bug.

So you are saying that I should use single record for result instead of multiple variables.
Ok, that works.

Another thing that was strange is that when I changed
declare _t1 s; to declare _t1 s[];
parsing passed, but of course it blew up at runtime.

Anyway, thanx for the workaround.
I'll see if it's faster comparing fields using record or as I do it currently.

Regards,
Rikard

- --
Rikard Pavelic
http://www.ngs.hr/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJRQtNTAAoJELBms/fUcNQT+s8IAIbnA1cShN7crCCphwgDZAXh
dqHW9V6m1ALPBBIgfhbjH3gPkj/5K9CC+jAGP1zRbHGunrgVKByiIcT+AoAnmGfB
8A7Vw69Yo6kQfi4hYtsEfAhmkpzn4/ZyY+q8+HwNgnAQytxRLg5rtQ5mJLard14U
rxyaZM2uFHGfAp//pkfzaL7pHGd1DC/MAFLnWftUUfraWXdvJmdjQSrunXDfvpYc
rfDSTTlHptK3esrk7UQnd0W9/GvpXjQNvbvgVEJ3YI0K/sUNit8QbL3DXxxOn64g
F+Pc8pIpFWC0RTC9u85VPmGLoA3MI5pkRMTEMxcIONscDbmZsxk13m0zDT3OdFs=
=VWnW
-----END PGP SIGNATURE-----