Thread: Create into temp table as select doesn set "found"

Hello,

I found an unexpected behavior, I suppose it is a little bug.

In plpgsql procedure I have this commads:

        create temp table t_expert on commit drop as
        select * from v_expert e where user_account=$1;

        get diagnostics rowcount=row_count;
        raise notice 'found=%, rowcount=%', found, rowcount;

The notice returned "found=f, rowcount=2"

I expected "found" should be set properly to true, when rowcount>0.
If not then I cannot use easy "if not found then ..." and I am forced to
write more rows in my code.

I wonder I have not found similar issue in any discussion yet.

With Regards
Jan Hink

Jan Hink <hink@rac.cz> writes:
> In plpgsql procedure I have this commads:

>         create temp table t_expert on commit drop as
>         select * from v_expert e where user_account=$1;

>         get diagnostics rowcount=row_count;
>         raise notice 'found=%, rowcount=%', found, rowcount;

> The notice returned "found=f, rowcount=2"

> I expected "found" should be set properly to true, when rowcount>0.

The set of statements that affect FOUND is specified here:
http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
CREATE TABLE AS is not among them.

In a green field we might think that it would be a good idea if it did,
but at this point in plpgsql's evolution changing that behavior would
be more likely to break existing code than to make users happier.
In particular, there is intentionally not a promise that FOUND and
ROW_COUNT are set by the exact same collections of statements.

            regards, tom lane