Обсуждение: hi let me know the solution to this question
Hi good morning all……..
Can u let me know if at all any system defined variable exists to check whether insert , update , and delete is successfully executed in a stored procedure……..
So that I can perform some steps based on the result of execution if possible please send me the sample code……………
Thanks & Regards
Penchal reddy | Software Engineer
Infinite Computer Solutions | Exciting Times…Infinite Possibilities...
SEI-CMMI level 5 | ISO 9001:2000
IT SERVICES | BPO
Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government
Tel +91-80-5193-0000(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com
Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at info.in@infics.com and delete this mail from your records.
| Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at info.in@infics.com and delete this email from your records. |
On Tue, Jul 18, 2006 at 10:46:29AM +0530, Penchalaiah P. wrote: > Can u let me know if at all any system defined variable exists to check > whether insert , update , and delete is successfully executed in a > stored procedure........ > > So that I can perform some steps based on the result of execution if > possible please send me the sample code............... Depending on what you mean by "successfully executed," see "Obtaining the Result Status" or "Trapping Errors" in the PL/pgSQL documentation (error trapping is available in 8.0 and later). http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- Michael Fuhr
On Tue, Jul 18, 2006 at 10:46:29AM +0530, Penchalaiah P. wrote:
> Can u let me know if at all any system defined variable exists to check
> whether insert , update , and delete is successfully executed in a
> stored procedure........
>
> So that I can perform some steps based on the result of execution if
> possible please send me the sample code...............
Depending on what you mean by "successfully executed," see "Obtaining
the Result Status" or "Trapping Errors" in the PL/pgSQL documentation
(error trapping is available in 8.0 and later).
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
For example, the following does NOT work but shows what I am trying to achieve:
CREATE OR REPLACE FUNCTION "public"."test_fn" () RETURNS VOID AS
'
DECLARE
mycount INTEGER;
BEGIN
-- This assignment will not work be I want something like it
-- so I can count the number of records updated.
mycount := EXECUTE
''update mytable '' ||
''mycolumn = 1 '' ||
''WHERE '' ||
'' mycolumn = 2 ''
;
RAISE NOTICE ''count = %'', mycount;
RETURN;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
I could not find any documentation about getting the count of updates but when you run the update by itself, PostgreSQL reports the number of records updated so I have to believe the information is available somewhere.
Thanks,
Aaron
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Aaron Bono wrote: > On 7/18/06, *Michael Fuhr* <mike@fuhr.org <mailto:mike@fuhr.org>> wrote: <snip> > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS <snip> > > OK, this question got me wondering: is there a way to determine, in a > function/stored procedure, the number of rows that were > inserted/updated/deleted? <snip> > I could not find any documentation about getting the count of updates > but when you run the update by itself, PostgreSQL reports the number of > records updated so I have to believe the information is available > somewhere. > > Thanks, > Aaron > Look for the section entitled "36.6.6. Obtaining the Result Status" on the link that Michael Fuhr supplied (above). Is that what you are looking for?
Aaron Bono wrote:
> On 7/18/06, *Michael Fuhr* <mike@fuhr.org <mailto:mike@fuhr.org>> wrote:
<snip>
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
<snip>
>
> OK, this question got me wondering: is there a way to determine, in a
> function/stored procedure, the number of rows that were
> inserted/updated/deleted?
<snip>
> I could not find any documentation about getting the count of updates
> but when you run the update by itself, PostgreSQL reports the number of
> records updated so I have to believe the information is available
> somewhere.
>
> Thanks,
> Aaron
>
Look for the section entitled "36.6.6. Obtaining the Result Status" on
the link that Michael Fuhr supplied (above). Is that what you are
looking for?
Ah yes, I missed the part on ROW_COUNT . That should do it.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================