Lists: | pgsql-general |
---|
From: | Muiz <work(dot)muiz(at)gmail(dot)com> |
---|---|
To: | questions(at)postgresql(dot)org |
Subject: | PLPGSQL: How can I get the effected rows when use "execute" command in function |
Date: | 2011-11-29 04:20:54 |
Message-ID: | CAF2hCqznYOcmn3DOBVHb_W443Oz+QqSe6MpCTgoPyz2j+YTO9w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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*
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Muiz <work(dot)muiz(at)gmail(dot)com> |
Cc: | questions(at)postgresql(dot)org |
Subject: | Re: PLPGSQL: How can I get the effected rows when use "execute" command in function |
Date: | 2011-11-29 13:59:57 |
Message-ID: | CAFj8pRDm=WYjL072dphxUZk-jmTnMM9=3Zch7LXgdqytebM6mg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hello
yes, you can. Look on GET DIAGNOSTICS statement
Regards
Pavel Stehule
2011/11/29 Muiz <work(dot)muiz(at)gmail(dot)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
>
>
>
From: | Ernesto Quiniones <ernestoq(at)gmail(dot)com> |
---|---|
To: | Muiz <work(dot)muiz(at)gmail(dot)com>, questions(at)postgresql(dot)org |
Subject: | Re: PLPGSQL: How can I get the effected rows when use "execute" command in function |
Date: | 2011-11-29 14:03:49 |
Message-ID: | 1322575429.12115.2.camel@Nokia-N900 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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*