Обсуждение: 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

>
> Thank you.
> Anna

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


> > 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
>
> >
> > Thank you.
> > Anna
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

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


> > 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
>
> >
> > Thank you.
> > Anna
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

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