 | | From: | KÖPFERL_Robert | | Subject: | Re: Returning a bool on DELETE in a proc. | | Date: | Wed, 19 Jan 2005 11:24:26 +0100 |
|
|
 | 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/
>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
|
| | From: | Richard Huxton | | Subject: | Re: Returning a bool on DELETE in a proc. | | Date: | Wed, 19 Jan 2005 12:16:50 +0000 |
|
|
| 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
|
| | From: | Karsten Hilbert | | Subject: | Re: Returning a bool on DELETE in a proc. | | Date: | Wed, 19 Jan 2005 14:18:47 +0100 |
|
|
| > >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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
|
|
| | From: | Michael Fuhr | | Subject: | Re: Returning a bool on DELETE in a proc. | | Date: | Wed, 19 Jan 2005 10:01:46 -0700 |
|
|
| 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/
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|
|
