PLPGSQL: How can I get the effected rows when use "execute" command in function
Dear all,
Can I get the effected rows after executing sqls in function?
e.g.:
CREATE OR REPLACE FUNCTION execsqls(sqls character varying)
RETURNS integer AS
$BODY$
DECLARE
BEGIN
EXECUTE sqls;
-- TODO-1: I want to know how many records the input sqls
effects?
RETURN effectedRows;
END;
$BODY$
LANGUAGE plpgsql;
test: select execsqls('update mytable where name like ''%abc''')
--
Regards,
*Muiz*
Hello
yes, you can. Look on GET DIAGNOSTICS statement
Regards
Pavel Stehule
2011/11/29 Muiz <work.muiz@gmail.com>:
Show quoted text
Dear all,
Can I get the effected rows after executing sqls in function?
e.g.:CREATE OR REPLACE FUNCTION execsqls(sqls character varying)
RETURNS integer AS
$BODY$
DECLARE
BEGIN
EXECUTE sqls;
-- TODO-1: I want to know how many records the input sqls
effects?
RETURN effectedRows;
END;
$BODY$
LANGUAGE plpgsql;test: select execsqls('update mytable where name like ''%abc''')
--
Regards,
Muiz
if you are doing insert, update or delete you can use "retuirng" command in the query, work with a cursor to get the rows
----- Mensaje original -----
Show quoted text
Dear all,
Can I get the effected rows after executing sqls in function?
e.g.:CREATE OR REPLACE FUNCTION execsqls(sqls character varying)
RETURNS integer AS
$BODY$
DECLARE
BEGIN
EXECUTE sqls;
-- TODO-1: I want to know how many records the input sqls
effects?
RETURN effectedRows;
END;
$BODY$
LANGUAGE plpgsql;test: select execsqls('update mytable where name like ''%abc''')
--
Regards,
*Muiz*