pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction return number of affected rows
7+ messages / 3 participants
[nested] [flat]
* Function return number of affected rows
@ 2005-02-10 11:34 Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
0 siblings, 1 reply; 7+ messages in thread
From: Francisco Figueiredo Jr. @ 2005-02-10 11:34 UTC (permalink / raw)
To: pgsql-interfaces
-----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-----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function return number of affected rows
@ 2005-02-10 14:46 Michael Fuhr <mike@fuhr.org>
parent: Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-02-10 14:46 UTC (permalink / raw)
To: Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>; +Cc: pgsql-interfaces
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function return number of affected rows
@ 2005-02-10 16:51 Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 7+ messages in thread
From: Francisco Figueiredo Jr. @ 2005-02-10 16:51 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function return number of affected rows
@ 2005-02-10 22:15 Michael Fuhr <mike@fuhr.org>
parent: Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Fuhr @ 2005-02-10 22:15 UTC (permalink / raw)
To: Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>; +Cc: pgsql-interfaces
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 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?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function return number of affected rows
@ 2005-02-12 18:42 Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 7+ messages in thread
From: Francisco Figueiredo Jr. @ 2005-02-12 18:42 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-interfaces
-----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-----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function return number of affected rows
@ 2005-02-12 19:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2005-02-12 19:08 UTC (permalink / raw)
To: Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function return number of affected rows
@ 2005-02-14 00:42 Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Francisco Figueiredo Jr. @ 2005-02-14 00:42 UTC (permalink / raw)
To: pgsql-interfaces
-----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-----
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-02-14 00:42 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-10 11:34 Function return number of affected rows Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
2005-02-10 14:46 ` Michael Fuhr <mike@fuhr.org>
2005-02-10 16:51 ` Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
2005-02-10 22:15 ` Michael Fuhr <mike@fuhr.org>
2005-02-12 18:42 ` Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
2005-02-12 19:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-14 00:42 ` Francisco Figueiredo Jr. <fxjrlists@yahoo.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox