Thread: Function return number of affected rows
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi all, I'd like to know if it is possible to get the number of affected rows of an insert, update or delete command inside a function. I'd like to know that, because I want to add this support, if possible, to Npgsql. I know the server sends a CompletedResponse message indicating the number of rows affected by a insert update and delete sent directly. But if this is inside a function? Is it possible to get this message too? Thanks in advance. - -- Regards, Francisco Figueiredo Jr. Membro Fundador do Projeto MonoBrasil - MonoBrasil Project Founder Member http://monobrasil.softwarelivre.org - ------------- "Science without religion is lame; religion without science is blind." ~ ~ Albert Einstein -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iQEVAwUBQgtGq/7iFmsNzeXfAQIcBwgAosSQMFPBlJZc7DkdOdyuKyLfwlNPHyrv 5aHcDsw0BH069oMzIpMF6xwEmrXrEeNfTzd1SU04uIHFesGf9ps3KZDr/BV/n3lU ocrREvxgor8lnFyVUxde1+FIe0fHWq0WlfPXkVISPDHXPRY68WICl8y3nzlZg6mJ NDDU4Du/i3YoE059ZXq0S30MKwMLqGAxXF+stp3wPjexSaZmG8p+hHj1dAyyhO39 RvKQP796nv3t0uQcAxvcHJquxwEhHEJWdvXjoSEvg8gTO+SatoMVP4Z4p//7ZmdB v3vY97yZIyI/58VnWiVaHWj8V80qwF/E0QVstI8X6GlASeV9ShRe4A== =NraD -----END PGP SIGNATURE-----
On Thu, Feb 10, 2005 at 09:34:04AM -0200, Francisco Figueiredo Jr. wrote: > > I'd like to know if it is possible to get the number of affected rows of > an insert, update or delete command inside a function. Yes, but how you get that information depends on your client interface. With libpq, for example, you could call PQcmdTuples(). If you're using something else then see the relevant documentation. > I'd like to know that, because I want to add this support, if possible, > to Npgsql. > > I know the server sends a CompletedResponse message indicating the > number of rows affected by a insert update and delete sent directly. But > if this is inside a function? Is it possible to get this message too? Does Npgsql use its own implemention of the PostgreSQL protocol? If so, then that implementation will have to provide the desired functionality itself. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote: > On Thu, Feb 10, 2005 at 09:34:04AM -0200, Francisco Figueiredo Jr. wrote: > >>I'd like to know if it is possible to get the number of affected rows of >>an insert, update or delete command inside a function. > > > Yes, but how you get that information depends on your client interface. > With libpq, for example, you could call PQcmdTuples(). If you're using > something else then see the relevant documentation. > Hi Michael. Thx for info. I will check what PQcmdTuples is doing. > >>I'd like to know that, because I want to add this support, if possible, >>to Npgsql. >> >>I know the server sends a CompletedResponse message indicating the >>number of rows affected by a insert update and delete sent directly. But >>if this is inside a function? Is it possible to get this message too? > > > Does Npgsql use its own implemention of the PostgreSQL protocol? > If so, then that implementation will have to provide the desired > functionality itself. > Yeap. Npgsql implements protocol itself. This is exactly the problem. I'm not receiving the CompletedResponse protocol message when executing the insert inside a function. It is through the CompletedResponse postgresql server says how many rows were affected. But it seems that when calling a function it doesn't return any. Thanks for your feedback Michael. Regards, Francisco Figueiredo Jr.
On Thu, Feb 10, 2005 at 02:51:46PM -0200, Francisco Figueiredo Jr. wrote:
> Michael Fuhr wrote:
> >Does Npgsql use its own implemention of the PostgreSQL protocol?
> >If so, then that implementation will have to provide the desired
> >functionality itself.
>
> Yeap. Npgsql implements protocol itself. This is exactly the problem.
> I'm not receiving the CompletedResponse protocol message when executing
> the insert inside a function. It is through the CompletedResponse
> postgresql server says how many rows were affected. But it seems that
> when calling a function it doesn't return any.
I haven't used Npgsql, but the documentation has an example with
the following code:
NpgsqlCommand command = new NpgsqlCommand("insert into table1 values(1, 1)", conn); Int32 rowsaffected; try {
rowsaffected= command.ExecuteNonQuery(); }
Is that what you're trying to do? Does it not work?
I downloaded the latest Npgsql source and found the following in
ExecuteNonQuery():
// The number of rows affected is in the third token for insert queries // and in the second token for update and
deletequeries. // In other words, it is the last token in the 0-based array.
return Int32.Parse(ret_string_tokens[ret_string_tokens.Length - 1]);
It looks like ExecuteNonQuery() is at least attempting to return
the number of rows affected.
Have I misunderstood what you're doing?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Michael Fuhr wrote:
Hi Michael!
|
| I haven't used Npgsql, but the documentation has an example with
| the following code:
|
| NpgsqlCommand command = new NpgsqlCommand("insert into table1
values(1, 1)", conn);
| Int32 rowsaffected;
|
| try
| {
| rowsaffected = command.ExecuteNonQuery();
| }
|
| Is that what you're trying to do? Does it not work?
|
Yeap. This works perfectly.
| I downloaded the latest Npgsql source and found the following in
| ExecuteNonQuery():
|
| // The number of rows affected is in the third token for insert queries
| // and in the second token for update and delete queries.
| // In other words, it is the last token in the 0-based array.
|
| return Int32.Parse(ret_string_tokens[ret_string_tokens.Length - 1]);
|
| It looks like ExecuteNonQuery() is at least attempting to return
| the number of rows affected.
|
| Have I misunderstood what you're doing?
|
I think I didn't make myself clear enough :)
When I send the update directly, Postgresql sends me back the
CompletedResponse and I can see how many rows were affected.
But if I create a function like:
create function inserttest() returns void as ' insert into table1
values(1, 1)' language 'sql';
and I do a select * from inserttest();
I can't get the CompletedResponse saying one row was affected :(
PS.: Michael, you can talk more protocol-specific way. I'm one of the
authors of Npgsql and I do have a little bit of protocol background. I
didn't talk directly about protocol and etc because I wanted to explain
my problem first :)
Also, I did a test with psql and I could check that it also doesn't say
me how many rows were affected. Can this mean that what I want may not
be possible?
Thank you very much for your feedback Michael.
- --
Regards,
Francisco Figueiredo Jr.
Membro Fundador do Projeto MonoBrasil - MonoBrasil Project Founder Member
http://monobrasil.softwarelivre.org
- -------------
"Science without religion is lame;
religion without science is blind."
~ ~ Albert Einstein
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iQEVAwUBQg5OEP7iFmsNzeXfAQLrAAf/XBi3nKXcye0S+AA4+jlKjMvJ6Vz0AOr+
/wRbBo7qJo0Zh7kdLC+ClYTeLBmVUUwMwOjqucJ+RqbrXHKbK0bSzbeKIhtRduAG
6pK3k2GUPYPy33EbEcPF8cBeD5E07xSgUEIPbt4ncKVU8IWVIcoTRDUPhQpCY9dF
2WQ5ZbFpvk4nEgrLxQLU08WVahTnqiqs+9xpLHw88sxeIfaNTM7Eow4doV//s2I7
2lKerVIyQrQ0RECHzRGwHTX93wa9s3h+sfbFDy+ZzTT3st4KXknbqqOFTEuwSMRd
24shXMmWZ8WzWU8bKrPmKG5ZICpNhwMg3dfWSbkjRETCkXsZeUQenA==
=KIXu
-----END PGP SIGNATURE-----
"Francisco Figueiredo Jr." <fxjrlists@yahoo.com.br> writes:
> But if I create a function like:
> create function inserttest() returns void as ' insert into table1
> values(1, 1)' language 'sql';
> and I do a select * from inserttest();
> I can't get the CompletedResponse saying one row was affected :(
Why should you? What you executed was a SELECT. What the function does
internally is no business of yours.
You could define the function as returning the information you would
like to have the client app know. I don't think there is any way to get
at the rowcount in a SQL-language function, but most of the PL languages
have a mechanism for it, e.g. GET DIAGNOSTICS integer_var = ROW_COUNT;
in plpgsql.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Tom Lane wrote: | "Francisco Figueiredo Jr." <fxjrlists@yahoo.com.br> writes: | |>But if I create a function like: | | |>create function inserttest() returns void as ' insert into table1 |>values(1, 1)' language 'sql'; | | |>and I do a select * from inserttest(); | | |>I can't get the CompletedResponse saying one row was affected :( | | | Why should you? What you executed was a SELECT. What the function does | internally is no business of yours. | Hi Tom. Ok. That was what I thougth too. But I just wanted to be sure I wasn't missing anything. | You could define the function as returning the information you would | like to have the client app know. I don't think there is any way to get | at the rowcount in a SQL-language function, but most of the PL languages | have a mechanism for it, e.g. GET DIAGNOSTICS integer_var = ROW_COUNT; | in plpgsql. | Yeap, that was one of the answers I got. I thought there could be some other way without modifying the function. I was asking that because I received a lot of requests of being able to get the number of rows affected by an executing function. And so I came here because I didn't see anything in protocol which could be used. Thanks for feedback Tom. - -- Regards, Francisco Figueiredo Jr. Membro Fundador do Projeto MonoBrasil - MonoBrasil Project Founder Member http://monobrasil.softwarelivre.org - ------------- "Science without religion is lame; religion without science is blind." ~ ~ Albert Einstein -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iQEVAwUBQg/z2P7iFmsNzeXfAQLPZAf/X1XBjDEEbIQ8oglHj/WKM+wCC6gnv7B9 ntXphAlMtDYQYvhVx0zht7zIy2sN3R31BeH254kk7rDyK68ssVc5G2Q4jX0zd0rx 2aedkk7M3hJlpO7/VK6QLJksZvBU8vYbjw5/UmC+uPfygjzkpV5UR+OSYRI9jrt1 Zr/Ce9C87IME52SwM5Tw4sFUTG1ogtFgnSLeuXw/jGhqweB3iBNBtdG4skd49ZRs GIg/StcZX92SOjiUF8eywMAFB5ar78tjJFLSgruHvO8JfeD0gtThBYDf5lclNImF rNNO4ql9Oe/2lfDiS9HFzVvz22hJ1OVF9AA+UoSEhWxVZWw2aMgzzQ== =RNmi -----END PGP SIGNATURE-----