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 <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > Date: > Thu, 2 Dec 2004 01:34:37 -0800 > > To: > "Tom Lane" <[EMAIL PROTECTED]> > CC: > <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> > > > Tom Lane wrote: > > >> Maksim Likharev <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] | (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 [EMAIL PROTECTED])
