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

[Rob Long]

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