Thread: Function return number of affected rows

Function return number of affected rows

From
"Francisco Figueiredo Jr."
Date:
-----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-----



Re: Function return number of affected rows

From
Michael Fuhr
Date:
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/


Re: Function return number of affected rows

From
"Francisco Figueiredo Jr."
Date:
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.




Re: Function return number of affected rows

From
Michael Fuhr
Date:
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/


Re: Function return number of affected rows

From
"Francisco Figueiredo Jr."
Date:
-----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-----



Re: Function return number of affected rows

From
Tom Lane
Date:
"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


Re: Function return number of affected rows

From
"Francisco Figueiredo Jr."
Date:
-----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-----