Обсуждение: 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,
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
Re: PLPGSQL: How can I get the effected rows when use "execute" command in function
От
Pavel Stehule
Дата:
Hello yes, you can. Look on GET DIAGNOSTICS statement http://www.postgresql.org/docs/9.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS Regards Pavel Stehule 2011/11/29 Muiz <work.muiz@gmail.com>: > 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 > > >
Re: PLPGSQL: How can I get the effected rows when use "execute" command in function
От
Ernesto Quiniones
Дата:
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 -----
> 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*