Thread: BUG #6551: PL/pgSQL: GET DIAGNOSTICS not working for first OUT parameter

The following bug has been logged on the website:

Bug reference:      6551
Logged by:          Adam Buraczewski
Email address:      aburacze@gmail.com
PostgreSQL version: 9.1.3
Operating system:   Linux (Fedora 16)
Description:=20=20=20=20=20=20=20=20

Hi!

I have just found strange behaviour of PL/pgSQL in case of using OUT
parameters and GET DIAGNOSTICS var =3D ROW_COUNT (PostgreSQL 9.1.3). Here i=
s a
self-contained example:

create table t (c integer);

create function p(out x1 integer, out x2 integer, out x3 integer)
as $$
begin
    insert into t values (1);
    get diagnostics x1 =3D row_count;
    insert into t values (2);
    get diagnostics x2 =3D row_count;
    insert into t values (3);
    get diagnostics x3 =3D row_count;
end;
$$ language plpgsql;

select * from p();

 x1 | x2 | x3=20
----+----+----
    |  1 |  1

Why x1 is NULL instead of the value 1? I found a workaround: declare a
temporary variable inside the function then assign ROW_COUNT to that
variable and then assign temporary variable to the OUT parameter. It works
but probably is not the solution one could expect:

create function p_workaround(out x1 integer, out x2 integer, out x3
integer)
as $$
declare
    tmpvar integer;
begin
    insert into t values (1);
    get diagnostics tmpvar =3D row_count;
    x1 :=3D tmpvar;
    insert into t values (2);
    get diagnostics x2 =3D row_count;
    insert into t values (3);
    get diagnostics x3 =3D row_count;
end;
$$ language plpgsql;

select * from p_workaround();

 x1 | x2 | x3=20
----+----+----
  1 |  1 |  1

Best regards,
Adam

<aburacze@gmail.com> wrote:

>  x1 | x2 | x3
> ----+----+----
>     |  1 |  1

To reduce the ambiguity about what value is coming from where, I
slightly modified the script before testing it against a recent HEAD
build:

create table t (c integer);

create function p(out x1 integer, out x2 integer, out x3 integer)
as $$
begin
    insert into t values (1),(2);
    get diagnostics x1 = row_count;
    insert into t values (3),(4),(5);
    get diagnostics x2 = row_count;
    insert into t values (6),(7),(8),(9),(10);
    get diagnostics x3 = row_count;
end;
$$ language plpgsql;

select * from p();

I got this:

 x1 | x2 | x3
----+----+----
    |  3 |  5
(1 row)

-Kevin

aburacze@gmail.com writes:
> I have just found strange behaviour of PL/pgSQL in case of using OUT
> parameters and GET DIAGNOSTICS var = ROW_COUNT (PostgreSQL 9.1.3). Here is a
> self-contained example:

Wow, that's been broken since the beginning, apparently.  There's an
entirely bogus test in exec_stmt_getdiag():

        if (diag_item->target <= 0)
            continue;

It's not clear what the point of this is, since there is no case in
which the target variable should be invalid, but in any case the effect
is to ignore attempts to assign to any variable with dno zero --- which
will be the one first defined in the function, eg the first parameter
if any.  I guess I can see how this escaped detection for a long time,
but it's broken for sure.  Thanks for the report!

            regards, tom lane

> Wow, that's been broken since the beginning, apparently. =A0There's an
> entirely bogus test in exec_stmt_getdiag():

Thanks for quick fix! :)

Best regards,

--=20
Adam Buraczewski