rowcount

Anna Dorofiyenko wrote:

Hello, list.
How can I get in PL/pgSQL number of rows affected by insert/update?

I'm looking to do something like:
insert into...
If rowcount>0
then ...

I believe its GET DIAGNOSTICS:

GET DIAGNOSTICS rowcount = ROW_COUNT;

The docs seem pretty paltry on the subject, but here's a link:

http://www.postgresql.org/idocs/index.php?plpgsql-statements.html

Anna Dorofiyenko wrote:

Hello, list.

How can I get in PL/pgSQL number of rows affected by insert/update?

I'm looking to do something like:

insert into...

If rowcount>0

then ...

See section 23.5.5 at:
http://www.postgresql.org/idocs/index.php?plpgsql-statements.html

Here's the part you want:
23.5.5. Obtaining result status

GET DIAGNOSTICS variable = item [ , ... ] ;

This command allows retrieval of system status indicators. Each item is
a keyword identifying a state value to be assigned to the specified
variable (which should be of the right data type to receive it). The
currently available status items are ROW_COUNT, the number of rows
processed by the last SQL query sent down to the SQL engine; and
RESULT_OID, the Oid of the last row inserted by the most recent SQL
query. Note that RESULT_OID is only useful after an INSERT query.

HTH,
Joe

That's correct.

DECLARE
rec_added int;

Begin
Insert.....
Get Diagnostics rec_added = ROW_COUNT;
IF rec_added > 0 then

Regards,
Sam

----- Original Message -----
From: "Mike Mascari" <mascarm@mascari.com>
To: "Anna Dorofiyenko" <anna.dorofiyenko@xdrive.com>
Cc: "Pgsql-General" <pgsql-general@postgresql.org>
Sent: Wednesday, May 01, 2002 3:02 PM
Subject: Re: [GENERAL] rowcount

This is exactly what I needed.
Thanks a lot.

-----Original Message-----
From: Samuel J. Sutjiono [mailto:ssutjiono@wc-group.com]
Sent: Wednesday, May 01, 2002 1:42 PM
To: Mike Mascari; Anna Dorofiyenko
Cc: Pgsql-General
Subject: Re: [GENERAL] rowcount

That's correct.

DECLARE
rec_added int;

Begin
Insert.....
Get Diagnostics rec_added = ROW_COUNT;
IF rec_added > 0 then

Regards,
Sam

----- Original Message -----
From: "Mike Mascari" <mascarm@mascari.com>
To: "Anna Dorofiyenko" <anna.dorofiyenko@xdrive.com>
Cc: "Pgsql-General" <pgsql-general@postgresql.org>
Sent: Wednesday, May 01, 2002 3:02 PM
Subject: Re: [GENERAL] rowcount

On Wed, 1 May 2002, Joe Conway wrote:

Anna Dorofiyenko wrote:

Hello, list.

How can I get in PL/pgSQL number of rows affected by insert/update?

I'm looking to do something like:

insert into...

If rowcount>0

then ...

See section 23.5.5 at:
http://www.postgresql.org/idocs/index.php?plpgsql-statements.html

Here's the part you want:
23.5.5. Obtaining result status

GET DIAGNOSTICS variable = item [ , ... ] ;

This command allows retrieval of system status indicators. Each item is
a keyword identifying a state value to be assigned to the specified
variable (which should be of the right data type to receive it). The
currently available status items are ROW_COUNT, the number of rows
processed by the last SQL query sent down to the SQL engine; and
RESULT_OID, the Oid of the last row inserted by the most recent SQL
query. Note that RESULT_OID is only useful after an INSERT query.

INSERT INTO <table> and not into a view, using a rule to do the real inserting,
fwiw.

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants