Re: Get diagnistic (row_count) 7.3 vs. 7.4 changes

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@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