Thread: 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,

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
>
>
>

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*