Thread: pl-pgsql question

pl-pgsql question

From
"Vilson farias"
Date:
Greetings,

  I'm writing some stored procedures using pl-pgsql and I need to know how
many tuples were affected by a update/delete/insert command.

  Let's suppose my script is like this :

CREATE FUNCTION gerachave(INT4) RETURNS INT4 AS'
DECLARE
  CAD       ALIAS FOR $1;
BEGIN
  novovalor := 0;

  UPDATE table1 SET is_ok = 1 WHERE cod_cad = CAD;

  INSERT INTO cnfg_gerachave VALUES (<quantity of tuples affected by
UPDATE>);

  RETURN CAD;
END;
'
LANGUAGE 'plpgsql';


Is it possible to discovery how many tuples were affected? How can I do it?

Thanks in advance.



----------------------------------------------------------------------------
----
José Vilson de Mello de Farias
Software Engineer

Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179


Re: pl-pgsql question

From
Darren Ferguson
Date:
rows INTEGER;

UPDATE foo SET bar = ''test'';
GET DIAGNOSTICS rows := ROW_COUNT;

This will return the number of rows actually updated by the system

HTH
Darren
Vilson farias wrote:

>Greetings,
>
>  I'm writing some stored procedures using pl-pgsql and I need to know how
>many tuples were affected by a update/delete/insert command.
>
>  Let's suppose my script is like this :
>
>CREATE FUNCTION gerachave(INT4) RETURNS INT4 AS'
>DECLARE
>  CAD       ALIAS FOR $1;
>BEGIN
>  novovalor := 0;
>
>  UPDATE table1 SET is_ok = 1 WHERE cod_cad = CAD;
>
>  INSERT INTO cnfg_gerachave VALUES (<quantity of tuples affected by
>UPDATE>);
>
>  RETURN CAD;
>END;
>'
>LANGUAGE 'plpgsql';
>
>
>Is it possible to discovery how many tuples were affected? How can I do it?
>
>Thanks in advance.
>
>
>
>----------------------------------------------------------------------------
>----
>José Vilson de Mello de Farias
>Software Engineer
>
>Dígitro Tecnologia Ltda - www.digitro.com.br
>APC - Customer Oriented Applications
>E-mail: vilson.farias@digitro.com.br
>Tel.: +55 48 281 7158
>ICQ 11866179
>
>
>---------------------------(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
>
>


Re: pl-pgsql question

From
Dennis Gearon
Date:
What's the scope of that statement, just the last statement within the same session, all statements within that
session,what? 

Darren Ferguson wrote:
> rows INTEGER;
>
> UPDATE foo SET bar = ''test'';
> GET DIAGNOSTICS rows := ROW_COUNT;
>
> This will return the number of rows actually updated by the system
>
> HTH
> Darren
> Vilson farias wrote:
>
>> Greetings,
>>
>>  I'm writing some stored procedures using pl-pgsql and I need to know how
>> many tuples were affected by a update/delete/insert command.
>>
>>  Let's suppose my script is like this :
>>
>> CREATE FUNCTION gerachave(INT4) RETURNS INT4 AS'
>> DECLARE
>>  CAD       ALIAS FOR $1;
>> BEGIN
>>  novovalor := 0;
>>
>>  UPDATE table1 SET is_ok = 1 WHERE cod_cad = CAD;
>>
>>  INSERT INTO cnfg_gerachave VALUES (<quantity of tuples affected by
>> UPDATE>);
>>
>>  RETURN CAD;
>> END;
>> '
>> LANGUAGE 'plpgsql';
>>
>>
>> Is it possible to discovery how many tuples were affected? How can I
>> do it?
>>
>> Thanks in advance.
>>
>>
>>
>> ----------------------------------------------------------------------------
>>
>> ----
>> José Vilson de Mello de Farias
>> Software Engineer
>>
>> Dígitro Tecnologia Ltda - www.digitro.com.br
>> APC - Customer Oriented Applications
>> E-mail: vilson.farias@digitro.com.br
>> Tel.: +55 48 281 7158
>> ICQ 11866179
>>
>>
>> ---------------------------(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
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


Re: pl-pgsql question

From
Darren Ferguson
Date:
Just the last statement that occurred in that session provided it is an
update / delete as far as i know

Darren

Dennis Gearon wrote:

> What's the scope of that statement, just the last statement within the
> same session, all statements within that session, what?
>
> Darren Ferguson wrote:
>
>> rows INTEGER;
>>
>> UPDATE foo SET bar = ''test'';
>> GET DIAGNOSTICS rows := ROW_COUNT;
>>
>> This will return the number of rows actually updated by the system
>>
>> HTH
>> Darren
>> Vilson farias wrote:
>>
>>> Greetings,
>>>
>>>  I'm writing some stored procedures using pl-pgsql and I need to
>>> know how
>>> many tuples were affected by a update/delete/insert command.
>>>
>>>  Let's suppose my script is like this :
>>>
>>> CREATE FUNCTION gerachave(INT4) RETURNS INT4 AS'
>>> DECLARE
>>>  CAD       ALIAS FOR $1;
>>> BEGIN
>>>  novovalor := 0;
>>>
>>>  UPDATE table1 SET is_ok = 1 WHERE cod_cad = CAD;
>>>
>>>  INSERT INTO cnfg_gerachave VALUES (<quantity of tuples affected by
>>> UPDATE>);
>>>
>>>  RETURN CAD;
>>> END;
>>> '
>>> LANGUAGE 'plpgsql';
>>>
>>>
>>> Is it possible to discovery how many tuples were affected? How can I
>>> do it?
>>>
>>> Thanks in advance.
>>>
>>>
>>>
>>> ----------------------------------------------------------------------------
>>> ----
>>> José Vilson de Mello de Farias
>>> Software Engineer
>>>
>>> Dígitro Tecnologia Ltda - www.digitro.com.br
>>> APC - Customer Oriented Applications
>>> E-mail: vilson.farias@digitro.com.br
>>> Tel.: +55 48 281 7158
>>> ICQ 11866179
>>>
>>>
>>> ---------------------------(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
>>>
>>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


Re: pl-pgsql question

From
Ron Johnson
Date:
On Thu, 2003-04-24 at 17:02, Darren Ferguson wrote:
> Just the last statement that occurred in that session provided it is an
> update / delete as far as i know
>
> Darren

Should also refer to INSERT INTO ... SELECT ... FROM.

> Dennis Gearon wrote:
>
> > What's the scope of that statement, just the last statement within the
> > same session, all statements within that session, what?
> >
> > Darren Ferguson wrote:
> >
> >> rows INTEGER;
> >>
> >> UPDATE foo SET bar = ''test'';
> >> GET DIAGNOSTICS rows := ROW_COUNT;
> >>
> >> This will return the number of rows actually updated by the system
> >>
> >> HTH
> >> Darren
> >> Vilson farias wrote:
> >>
> >>> Greetings,
> >>>
> >>>  I'm writing some stored procedures using pl-pgsql and I need to
> >>> know how
> >>> many tuples were affected by a update/delete/insert command.
> >>>
> >>>  Let's suppose my script is like this :
> >>>
> >>> CREATE FUNCTION gerachave(INT4) RETURNS INT4 AS'
> >>> DECLARE
> >>>  CAD       ALIAS FOR $1;
> >>> BEGIN
> >>>  novovalor := 0;
> >>>
> >>>  UPDATE table1 SET is_ok = 1 WHERE cod_cad = CAD;
> >>>
> >>>  INSERT INTO cnfg_gerachave VALUES (<quantity of tuples affected by
> >>> UPDATE>);
> >>>
> >>>  RETURN CAD;
> >>> END;
> >>> '
> >>> LANGUAGE 'plpgsql';
> >>>
> >>>
> >>> Is it possible to discovery how many tuples were affected? How can I
> >>> do it?
> >>>
> >>> Thanks in advance.

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| Regarding war zones: "There's nothing sacrosanct about a  |
| hotel with a bunch of journalists in it."                 |
|     Marine Lt. Gen. Bernard E. Trainor (Retired)          |
+-----------------------------------------------------------+