pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhandling error in a function
3+ messages / 3 participants
[nested] [flat]
* handling error in a function
@ 2002-12-17 19:42 Peter Gabriel <Peter.Gabriel@gmx.de>
2002-12-18 10:07 ` Re: handling error in a function Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 3+ messages in thread
From: Peter Gabriel @ 2002-12-17 19:42 UTC (permalink / raw)
To: pgsql-sql
Hi all!
i made desperate efforts with handling errors in a function.
I am using functions for encapsulating a few sql-statements. Please have a
look at this:
CREATE FUNCTION sp_fdelce(int4) RETURNS int4 AS '
DECLARE
id ALIAS FOR $1;
BEGIN
DELETE FROM f_ces WHERE fce_id = id;
-- "virtual code follows"
IF ERROR
RETURN 0;
ELSE
RETURN 1;
END;
'
LANGUAGE 'plpgsql';
Not difficult. I know. But fce_id is used as a foreign key by other tables.
When executing this and violating that constraint (i mustn't delete that
row), the function aborts with "unknown error" and i have no way to return 0 or
something like that.
I am programming with PHP and PEAR, each time, the result set is an object
of type error, the script jumps to an error page, to calm the angry customers.
Especially in this case I don't want to jump to the error page, i want to
tell the user with a normal Messageline: Sorry, you mustn't delete that
element.
I can't handle this error? Is that right? I really have no way to catch that
foreign key violence?
(In MS-SQL there is @@ERROR to indicate a database error and i can handle
it, in Oracle nearly the same ...)
Please help :-)
Regards,
peter
--
+++ GMX - Mail, Messaging & more http://www.gmx.net +++
NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: handling error in a function
2002-12-17 19:42 handling error in a function Peter Gabriel <Peter.Gabriel@gmx.de>
@ 2002-12-18 10:07 ` Tomasz Myrta <jasiek@klaster.net>
0 siblings, 0 replies; 3+ messages in thread
From: Tomasz Myrta @ 2002-12-18 10:07 UTC (permalink / raw)
To: Peter Gabriel <Peter.Gabriel@gmx.de>; +Cc: pgsql-sql
Peter Gabriel wrote:
>
> When executing this and violating that constraint (i mustn't delete that
> row), the function aborts with "unknown error" and i have no way to
> return 0 or
> something like that.
>
> I am programming with PHP and PEAR, each time, the result set is an object
> of type error, the script jumps to an error page, to calm the angry
> customers.
I don't know, how PHP works with Postgresql, but in psql I get such
error, when deleting wrong row:
ERROR: <unnamed> referential integrity violation - key in table_master
still referenced from table_detail
Maybe it is possible some way to access this error inside PHP?
Tomasz Myrta
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: handling error in a function
@ 2002-12-18 09:04 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 3+ messages in thread
From: Christoph Haller @ 2002-12-18 09:04 UTC (permalink / raw)
To: pgsql-sql; +Cc: Peter.Gabriel@gmx.de
>
> i made desperate efforts with handling errors in a function.
>
> I am using functions for encapsulating a few sql-statements. Please
have a
> look at this:
>
> CREATE FUNCTION sp_fdelce(int4) RETURNS int4 AS '
> DECLARE
> id ALIAS FOR $1;
> BEGIN
> DELETE FROM f_ces WHERE fce_id = id;
>
> -- "virtual code follows"
> IF ERROR
> RETURN 0;
> ELSE
> RETURN 1;
> END;
> '
> LANGUAGE 'plpgsql';
>
> Not difficult. I know. But fce_id is used as a foreign key by other
tables.
> When executing this and violating that constraint (i mustn't delete
that
> row), the function aborts with "unknown error" and i have no way to
return 0 or
> something like that.
>
> I am programming with PHP and PEAR, each time, the result set is an
object
> of type error, the script jumps to an error page, to calm the angry
customers.
>
>
> Especially in this case I don't want to jump to the error page, i want
to
> tell the user with a normal Messageline: Sorry, you mustn't delete
that
> element.
>
> I can't handle this error? Is that right? I really have no way to
catch that
> foreign key violence?
>
Sure you can, but not directly.
Before deleting you should check for the error condition,
possibly by querying system tables especially pg_relcheck.
So, if the error condition matches, don't delete but generate
your message line.
Regards, Christoph
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-12-18 10:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-17 19:42 handling error in a function Peter Gabriel <Peter.Gabriel@gmx.de>
2002-12-18 10:07 ` Tomasz Myrta <jasiek@klaster.net>
2002-12-18 09:04 Re: handling error in a function Christoph Haller <ch@rodos.fzk.de>
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