Get diagnistic (row_count) 7.3 vs. 7.4 changes

Hi,
Just trying 7.4.5 and bumped into a problem.

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

Is it known behavior change or something else.
Thank you.

Maksim Likharev <MLikharev(at)micropat(dot)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

Tom Lane wrote:
> Maksim Likharev <MLikharev(at)micropat(dot)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

On my opinion row_count should not be null due to the last operation
produced some records, copied to the temp table.

Basically it's irrelevant for me whether it fixed or not, I'll have to port
this code fast and going to use select count.

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Wednesday, December 01, 2004 5:53 PM
> To: MLikharev(at)micropat(dot)com
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes
>
>
> Maksim Likharev <MLikharev(at)micropat(dot)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