pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning a bool on DELETE in a proc.
8+ messages / 4 participants
[nested] [flat]
* Returning a bool on DELETE in a proc.
@ 2005-01-18 16:04 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-01-18 18:52 ` Re: Returning a bool on DELETE in a proc. Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 8+ messages in thread
From: KÖPFERL Robert @ 2005-01-18 16:04 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning a bool on DELETE in a proc.
2005-01-18 16:04 Returning a bool on DELETE in a proc. KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-01-18 18:52 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 8+ messages in thread
From: Michael Fuhr @ 2005-01-18 18:52 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning a bool on DELETE in a proc.
@ 2005-01-19 10:24 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-01-19 12:16 ` Re: Returning a bool on DELETE in a proc. Richard Huxton <dev@archonet.com>
2005-01-19 17:01 ` Re: Returning a bool on DELETE in a proc. Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 8+ messages in thread
From: KÖPFERL Robert @ 2005-01-19 10:24 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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/
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning a bool on DELETE in a proc.
2005-01-19 10:24 Re: Returning a bool on DELETE in a proc. KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-01-19 12:16 ` Richard Huxton <dev@archonet.com>
2005-01-19 13:18 ` Re: Returning a bool on DELETE in a proc. Karsten Hilbert <Karsten.Hilbert@gmx.net>
1 sibling, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2005-01-19 12:16 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning a bool on DELETE in a proc.
2005-01-19 10:24 Re: Returning a bool on DELETE in a proc. KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-01-19 12:16 ` Re: Returning a bool on DELETE in a proc. Richard Huxton <dev@archonet.com>
@ 2005-01-19 13:18 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 0 replies; 8+ messages in thread
From: Karsten Hilbert @ 2005-01-19 13:18 UTC (permalink / raw)
To: pgsql-sql
> >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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning a bool on DELETE in a proc.
2005-01-19 10:24 Re: Returning a bool on DELETE in a proc. KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-01-19 17:01 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Fuhr @ 2005-01-19 17:01 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning a bool on DELETE in a proc.
@ 2005-01-19 17:09 KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-01-19 21:35 ` Re: Returning a bool on DELETE in a proc. Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 8+ messages in thread
From: KÖPFERL Robert @ 2005-01-19 17:09 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
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/
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Returning a bool on DELETE in a proc.
2005-01-19 17:09 Re: Returning a bool on DELETE in a proc. KÖPFERL Robert <robert.koepferl@sonorys.at>
@ 2005-01-19 21:35 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 8+ messages in thread
From: Michael Fuhr @ 2005-01-19 21:35 UTC (permalink / raw)
To: KÖPFERL Robert <robert.koepferl@sonorys.at>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-01-19 21:35 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-18 16:04 Returning a bool on DELETE in a proc. KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-01-18 18:52 ` Michael Fuhr <mike@fuhr.org>
2005-01-19 10:24 Re: Returning a bool on DELETE in a proc. KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-01-19 12:16 ` Richard Huxton <dev@archonet.com>
2005-01-19 13:18 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2005-01-19 17:01 ` Michael Fuhr <mike@fuhr.org>
2005-01-19 17:09 Re: Returning a bool on DELETE in a proc. KÖPFERL Robert <robert.koepferl@sonorys.at>
2005-01-19 21:35 ` Michael Fuhr <mike@fuhr.org>
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