PGSQL GENERAL 9 RE GET DIAGNISTIC ROW COUNT 7 3 VS 7 4 CHANGES
From: RLong@no-spam (Rob Long)
Subject: Re: Get diagnistic (row_count) 7.3 vs. 7.4 changes
Date: Wed, 08 Dec 2004 14:07:53 -0500


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 in 7.4.5 as the documentation indicates that this should work as described?


Thanks in advance,
Rob
Maksim Likharev <MLikharev@no-spam> 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@no-spam>
Date:
Thu, 2 Dec 2004 01:34:37 -0800

To:
"Tom Lane" <tgl@no-spam>
CC:
<MLikharev@no-spam>, <pgsql-general@no-spam>

Tom Lane wrote:

>> Maksim Likharev <MLikharev@no-spam> 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

From: pgman@no-spam (Bruce Momjian)
Subject: Re: Get diagnistic (row_count) 7.3 vs. 7.4 changes
Date: Sat, 18 Dec 2004 23:43:22 -0500 (EST)

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 in 7.4.5 as the documentation indicates that this should work as described?

> > Thanks in advance,
> Rob > > > > Maksim Likharev <MLikharev@no-spam> 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@no-spam>
> Date:
> Thu, 2 Dec 2004 01:34:37 -0800
> > To:
> "Tom Lane" <tgl@no-spam>
> CC:
> <MLikharev@no-spam>, <pgsql-general@no-spam>
> > > Tom Lane wrote:
> > >> Maksim Likharev <MLikharev@no-spam> 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@no-spam | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@no-spam