Re: [HACKERS] [JDBC] Where are we on stored procedures?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Markus Schaber wrote: | Hi, Francisco, | | Francisco Figueiredo Jr. schrieb: | | |>In fact, I think people keep requesting me support on Npgsql for that |>because MS Sql server supports it and they are porting their code to use |>Postgresql and facing that difficult. |> |>Indeed, for (a) we could use an approach similar to Ms sql server. We |>could have a way of the procedure say if it wanted to send the rows |>affected information or not. |>And about (b) I think that it is only on trivial cases that people |>really want to use this feature :) | | | As you need to tweak the function/procedure source to implement (a), you | can also tweak the function/procedure to return the row cound and | whatever other diagnostics you need. Yeap, You are right. | | For newly implemented stored procedures, we could create a special | diagnostics result set that every procedure creates. This usually | contains some success/error information, but the procedure could add | additional rows to it. | I think this could be a very good idea. I think you could create an special tag which would say to create this special resultset or not as, I think, not every stored procedure would need it. I think this would be very nice. Thanks Markus, for feedback. - -- 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 iQEVAwUBQiPPOf7iFmsNzeXfAQLFYQgAkiBLkYi8lKQ6NNJ2qP48nH8PmODoe4Wp FPB7lNvo9fLw0aw5rbztcu19FtvcJBJuFBFPc2LKImpszuJ2hcD02pTGqx3UxMR8 Yz3edTijCziHg8uBTiXQV0vHZ5WeE2/sEp+ve5heanDdzAcwLiCfDPxTR1XfVYaP AJfLIHspwVkrXcEbtgwEdPX91QG/cLYjcRR/fhiaH4s8I3Hi3o9ZitQ4bkdOdosw 10+TMrgzbtXKdEOhWu9xgvdcujksjKi1xXXXSwr+L5WPi4y9iohnIi6X9j9wWlOw fr9p2lYJgfzpq71Cl8dRlW+d0pRJFUlHiNaT/Adhzwsozc459Vix8Q== =Jcuy -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [JDBC] Where are we on stored procedures?
Hi, Francisco, Francisco Figueiredo Jr. schrieb: > In fact, I think people keep requesting me support on Npgsql for that > because MS Sql server supports it and they are porting their code to use > Postgresql and facing that difficult. > > Indeed, for (a) we could use an approach similar to Ms sql server. We > could have a way of the procedure say if it wanted to send the rows > affected information or not. > And about (b) I think that it is only on trivial cases that people > really want to use this feature :) As you need to tweak the function/procedure source to implement (a), you can also tweak the function/procedure to return the row cound and whatever other diagnostics you need. For newly implemented stored procedures, we could create a special diagnostics result set that every procedure creates. This usually contains some success/error information, but the procedure could add additional rows to it. Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com signature.asc Description: OpenPGP digital signature
Re: [HACKERS] [JDBC] Where are we on stored procedures?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: | "Francisco Figueiredo Jr." <[EMAIL PROTECTED]> writes: | |>Could I add another item? | | |>Could we have the row count of statements executed inside a |>procedure/function returned to client? | | | IMHO that request is completely bogus; if the procedure wants to tell | the client that, it's the procedure's responsibility to return the | number as a result. Doing what you ask (a) would arguably be a security | violation, and (b) the info would be impossible for the client to | interpret in any but the most trivial cases anyway. Consider for | instance a procedure that contains different queries in the THEN and | the ELSE arms of an IF, or contains loops executed a variable number | of times. Without access to the results of the control flow tests, the | client could not know which number means what. Hi Tom Lane. Thanks for feedback. In fact, I think people keep requesting me support on Npgsql for that because MS Sql server supports it and they are porting their code to use Postgresql and facing that difficult. Indeed, for (a) we could use an approach similar to Ms sql server. We could have a way of the procedure say if it wanted to send the rows affected information or not. And about (b) I think that it is only on trivial cases that people really want to use this feature :) But thinking on other side I think this in only a differ in backend's idiom. I already answered your solution of get diagnostics and I will provide a litle FAQ about it so people can see how it is done on postgresql. Regards, Francisco Figueiredo Jr. - -- 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 iQEVAwUBQh854P7iFmsNzeXfAQJ7UQf+Jqd4G7EE7YFyDLRBsuoGLZTYl7N/ZY0M yeIgRbTNw3HPeHb+OiAI+MMXCWxu0YU4OgLOvqkls9NKHkeuMjdSwmsCfwPL0qfc sZT6UqQJyap6skH1VJ7jQEhfgjesfpqYzUoyeJUJDiRyFsEbM4sbdnDdpMawf/jq jgzx50lpgQtFOblsaMlfklLiUZa47FeMC8M64XcmzE+IIdfJcx0kWYQ+LKtoimNs 9SH7Z1v3lnNKvucLSw5eMT/y2Ldu8Qgr4/eK7JroKcI4JbHOlNtirrovAoinBjwB Otcwtd2tRqffFCrF6MqsSzuXn+S2C4Wj9/0qWZ1CIzevigb+QfXOIw== =h32q -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [JDBC] Where are we on stored procedures?
"Francisco Figueiredo Jr." <[EMAIL PROTECTED]> writes: > Could I add another item? > Could we have the row count of statements executed inside a > procedure/function returned to client? IMHO that request is completely bogus; if the procedure wants to tell the client that, it's the procedure's responsibility to return the number as a result. Doing what you ask (a) would arguably be a security violation, and (b) the info would be impossible for the client to interpret in any but the most trivial cases anyway. Consider for instance a procedure that contains different queries in the THEN and the ELSE arms of an IF, or contains loops executed a variable number of times. Without access to the results of the control flow tests, the client could not know which number means what. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [JDBC] Where are we on stored procedures?
--- Tom Lane <[EMAIL PROTECTED]> escreveu: > Gavin and Neil made some noise in late September about implementing > stored procedures for PG 8.1, but I haven't heard anything more about > it since that thread died off. I've been getting some pressure inside > Red Hat to see us support more of the JDBC CallableProcedure spec, so > I'd like to reopen the discussion. > I don't know if I can talk about it here, but here I go. Please let me know if it is not appropriate. > In the previous discussion starting here: > http://archives.postgresql.org/pgsql-hackers/2004-09/msg00702.php > it seemed that we were bandying around several different issues. > People wanted "procedures" to differ from our current implementation > of "functions" in such ways as: > > > 2. Having OUT parameters, and perhaps also INOUT parameters. At least > in the JDBC spec, these are seen as scalar values, and so the feature > is really just syntactic sugar for returning a composite type or row > value. For instance, a procedure foo(x IN int, y OUT text, z OUT float) > could perhaps be called via > SELECT y, z FROM foo(42); > where foo(x) is seen as returning the rowtype (y text, z float). > That would be very good. Could I add another item? Could we have the row count of statements executed inside a procedure/function returned to client? I know Tom Lane, that you already talked about that this could be unnecessary, but we from Npgsql sometimes get reports about the support of needing to know the number of rows affected by a function/procedure call. You told me about the get diagnostics ROWCOUNT and it worked like a charm, but I was thinking about something without the need to change the procedure, as even this modification needs some aware of client to be able to get the result. Please, jdbc guys, if you know some easy way of doing this, please let me know so I can implement in Npgsql. Thanks in advance. Regards, Francisco Figueiredo Jr. __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
