Thread: pl-pgsql question
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
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 > >
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 >
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)
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) | +-----------------------------------------------------------+