Thread: Returning a bool on DELETE in a proc.
Hi,
I'm currently writing a function which encapsulates a delete and should
return a bool as indicator for success.
I tried:
DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and "BNumberPrefix"=$2; SELECT TRUE;
but this makes me not happy.
How can I distingruish wehter DELETE affected 0 or more rows and return that
while DELETE is not capable of returning any value?
And is the whole function executed if the DELETE fails or will it stop
bevore the select?
I had a read throu SQL-functions while nothing matched me.
> Hi, > > I'm currently writing a function which encapsulates a delete and should > return a bool as indicator for success. > > I tried: > > DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and "BNumberPrefix"=$2; > SELECT TRUE; > > but this makes me not happy. > How can I distingruish wehter DELETE affected 0 or more rows and > return that while DELETE is not capable of returning any value? > > And is the whole function executed if the DELETE fails or will it > stop bevore the select? > > I had a read throu SQL-functions while nothing matched me. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster Robert, I do not know what version you are working with but here is the 7.4 documentation on result status. http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS Kind Regards, Keith PS I am a novice myself so don't put too much faith in my post. ;-) ______________________________________________ 99main Internet Services http://www.99main.com
On Tue, Jan 18, 2005 at 05:04:58PM +0100, KÖPFERL Robert wrote: > I'm currently writing a function which encapsulates a delete and should > return a bool as indicator for success. How do you define success? When the delete affects one or more rows? > DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and "BNumberPrefix"=$2; > SELECT TRUE; > > but this makes me not happy. > How can I distingruish wehter DELETE affected 0 or more rows and return that > while DELETE is not capable of returning any value? If you're using PL/pgSQL then see "Obtaining the Result Status" in the "Basic Statements" section of the PL/pgSQL documentation. > And is the whole function executed if the DELETE fails or will it stop > bevore the select? A PL/pgSQL function should exit immediately with an error if the DELETE fails. But understand that PL/pgSQL's notion of a failure might not match yours: trying to delete from a non-existent table is a failure, but deleting zero rows from an existing table isn't. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Thanks, but that was not what I was looking for. Seems that I forgot to mention that this function is intended to be written in SQL, not in PL/SQL. For the second one I already picked it off the fascinating* doc (as Keith did) that GET DIAGONSTIC is the right thing. So what variable/function is the correct SQL-equivalent to ROW_COUNT and can it be used in the following statement ? like DELETE...; SELECT (ROW_COUNT<>0); to return a bool value? Or would you really suggest to change the fct's type to void? *The doc is fascinating, because it is rather short in terms of PL/pgSQL but if you are looking for something you can find it in some example or mentioned in a sentence somweheewere > -----Original Message----- > From: Michael Fuhr [mailto:mike@fuhr.org] > Sent: Dienstag, 18. Jänner 2005 19:52 > To: KÖPFERL Robert > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Returning a bool on DELETE in a proc. > > > On Tue, Jan 18, 2005 at 05:04:58PM +0100, KÖPFERL Robert wrote: > > > I'm currently writing a function which encapsulates a > delete and should > > return a bool as indicator for success. > > How do you define success? When the delete affects one or more rows? > > > DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and > "BNumberPrefix"=$2; > > SELECT TRUE; > > > > but this makes me not happy. > > How can I distingruish wehter DELETE affected 0 or more > rows and return that > > while DELETE is not capable of returning any value? > > If you're using PL/pgSQL then see "Obtaining the Result Status" in > the "Basic Statements" section of the PL/pgSQL documentation. > > > And is the whole function executed if the DELETE fails or > will it stop > > bevore the select? > > A PL/pgSQL function should exit immediately with an error if the > DELETE fails. But understand that PL/pgSQL's notion of a failure > might not match yours: trying to delete from a non-existent table > is a failure, but deleting zero rows from an existing table isn't. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ >
KÖPFERL Robert wrote: > Thanks, but that was not what I was looking for. > > Seems that I forgot to mention that this function is intended to be written > in SQL, not in PL/SQL. For the second one I already picked it off the > fascinating* doc (as Keith did) that GET DIAGONSTIC is the right thing. > > So what variable/function is the correct SQL-equivalent to ROW_COUNT and can > it be used in the following statement ? > like DELETE...; SELECT (ROW_COUNT<>0); to return a bool value? SQL doesn't support that (although I suppose it could be made to with some pg_rows_affected() function). You'll need to use plpgsql (or similar) if you want a result status returned. -- Richard Huxton Archonet Ltd
> >So what variable/function is the correct SQL-equivalent to ROW_COUNT and > >can it be used in the following statement ? > >like DELETE...; SELECT (ROW_COUNT<>0); to return a bool value? > > SQL doesn't support that (although I suppose it could be made to with > some pg_rows_affected() function). If someone could only post that pg_rows_affected() function - this would end all further requests for "row number in result set" inquiries with the simple advice "use pg_rows_affected() and join that with an integer table". I'd know a bunch of uses right off the top of my head. I suppose it ain't hard at all but my C skills are way to limited to try anything like that. My understanding, too, likely. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Wed, Jan 19, 2005 at 11:24:26AM +0100, KÖPFERL Robert wrote: > Seems that I forgot to mention that this function is intended to be written > in SQL, not in PL/SQL. Why SQL instead of PL/pgSQL if the former doesn't do what you need and the latter does? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
It's ... hm I have the feeling that even complicated and boxed queries are faster or can be optimized by the planer than any PLpgsql. Isn't there a motule that has to be invoked? I'm capable to learn. > -----Original Message----- > From: Michael Fuhr [mailto:mike@fuhr.org] > Sent: Mittwoch, 19. Jänner 2005 18:02 > To: KÖPFERL Robert > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Returning a bool on DELETE in a proc. > > > On Wed, Jan 19, 2005 at 11:24:26AM +0100, KÖPFERL Robert wrote: > > > Seems that I forgot to mention that this function is > intended to be written > > in SQL, not in PL/SQL. > > Why SQL instead of PL/pgSQL if the former doesn't do what you need > and the latter does? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ >
On Wed, Jan 19, 2005 at 06:09:16PM +0100, KÖPFERL Robert wrote: > > > > Why SQL instead of PL/pgSQL if the former doesn't do what you need > > and the latter does? > > I have the feeling that even complicated and boxed queries are faster or can > be optimized by the planer than any PLpgsql. Isn't there a motule that has > to be invoked? PL/pgSQL might be more efficient than you think -- see the Overview in the PL/pgSQL documentation for more information about how it works. I'd suggest making the function work first, and only then worry about whether it's fast enough. -- Michael Fuhr http://www.fuhr.org/~mfuhr/