Re: Get diagnistic (row_count) 7.3 vs. 7.4 changes - Mailing list pgsql-general

Because the GET DIAGNOSTICS is after the CREATE TEMP TABLE command, I
think zero is the right value, rather than the number of rows in the
SELECT.  I can see why it was handy to do it the old way in 7.3 but it
seems it was a byproduct of GET DIAGNOSTICS not working properly.

I suppose the only clean way to do it now is to do a SELECT COUNT().

---------------------------------------------------------------------------

Rob Long wrote:
> Hello.
>
> Seeking further clarity regarding GET DIAGNOSTICS behavior in 7.4.
>
> As described previously GET DIAGNOSTICS in the following example does not work in 7.4.5:
>
> CREATE OR REPLACE FUNCTION "public"."rowcount_test" () RETURNS bigint AS'
> DECLARE
>        base_hits bigint;
>  BEGIN
>
>      base_hits := 0;
>
>      CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data;
>      GET DIAGNOSTICS base_hits = ROW_COUNT;
>
>      RETURN base_hits;
>  END;
> 'LANGUAGE 'plpgsql' VOLATILE
>
> Base_hits returns 0 and not 1 while 7.3 returns 1.  Without base_hits := 0, null would be returned.
>
> Output:
>
> 7.3.3
> queriesdbtest=# select * from public.rowcount_test();
>  rowcount_test
> ---------------
>              1
> (1 row)
>
> 7.4.5
> queriesdbtest=# select * from public.rowcount_test();
>  rowcount_test
> ---------------
>              0
> (1 row)
>
> What is the preferred/recommended way for obtaining rows worked with via the last SQL statement?  Can this be a bug
in7.4.5 as the documentation indicates that this should work as described? 
>
> Thanks in advance,
> Rob
>
>
>
> Maksim Likharev <MLikharev@micropat.com> writes:
>
> >> consider following code:
> >
> >
>
> >> CREATE OR REPLACE FUNCTION rowcount_test() RETURNS bigint AS '
> >> DECLARE
> >>     base_hits bigint;
> >> BEGIN
> >>     CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data;
> >>     GET DIAGNOSTICS base_hits = ROW_COUNT;
> >
> >
>
> >>     RETURN base_hits;
> >> END;
> >> ' LANGUAGE PLPGSQL VOLATILE;
> >
> >
>
> >> in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into a temp
> >> table
> >> in 7.4.5 GET DIAGNOSTICS returns 0
> >
> >
>
> Hmm.  I'm not sure if that's a bug or an improvement.  The command did
> not return any rows to plpgsql, so in that sense row_count = 0 is
> correct, but I can see why you feel you've lost some capability.
>
> Anyone else have an opinion about this?
>
>             regards, tom lane
>
>
> ------------------------------------------------------------------------
>
> Subject:
> Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes
> From:
> "Richard Huxton" <dev@archonet.com>
> Date:
> Thu, 2 Dec 2004 01:34:37 -0800
>
> To:
> "Tom Lane" <tgl@sss.pgh.pa.us>
> CC:
> <MLikharev@micropat.com>, <pgsql-general@postgresql.org>
>
>
> Tom Lane wrote:
>
> >> Maksim Likharev <MLikharev@micropat.com> writes:
> >>
> >
> >
> >>>> in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into
> >>>> a temp table in 7.4.5 GET DIAGNOSTICS returns 0
> >>
> >>
> >>
> >>
> >> Hmm.  I'm not sure if that's a bug or an improvement.  The command
> >> did not return any rows to plpgsql, so in that sense row_count = 0 is
> >>  correct, but I can see why you feel you've lost some capability.
> >>
> >> Anyone else have an opinion about this?
> >
> >
>
> Well, from the manuals:
> "The currently available status items are ROW_COUNT, the number of rows
> processed by the last SQL command sent down to the SQL engine"
>
> Nothing there about rows being returned.
>
> And by analogy:
> "A PERFORM statement sets FOUND true if it produces (and discards) a
> row, false if no row is produced."
>
> If you've FOUND rows then presumably ROW_COUNT should be non-zero. So
> set it if rows aren't returned I'd opine.
>
> --
>    Richard Huxton
>    Archonet Ltd
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073