Re: no "ON INSERT ignore" in postgre?

Le Vendredi 5 Avril 2002 02:39, vous avez écrit :
> In Foxpro,
> this was implemented likewise with "ignore" on insert
> into zBilling since there is actually a one-to-three
> relationship and not the one-to-one needed to restrict
> inserts.
>
> Please Help! ( I really do not relish the idea of
> rewriting the frontend application because of
> "insert". And even if I did, I don't know how I could
> rework the 1-to-3 from zbilling to zpolicy, zcompany,
> or zpeople).

Dear Alan,

Maybe you should CC pgsql-general so that anyone can participate.

There are many ways to handle restriction in PostgreSQL :
- Foreign keys
- Rules
- Triggers

Foreign keys are too simple to be used in your case. Rules and Triggers
differ because rules REWRITE sql queries on the fly whereas triggers are
ACTIONS performed before insert/delete/update.

In my applications, I use triggers, mostly because I do not have a good
knowledge of writting rules. A purist would probably use rules ... but let's
do it with triggers.

You probably need a server-side language (like Pgplsql or pgpython) as it
will give you more features than pure SQL. Here is a Plpgsql example :

**********************
CREATE FUNCTION "tg_term_source_d"() RETURNS "opaque" AS 'DECLARE
rec record;

BEGIN

SELECT INTO rec target_oid
FROM translationforge_term_target
WHERE target_oidsource = old.source_oid
AND target_content <> '''';

IF NOT FOUND THEN
-- write custom action (example : delete some cascade records)
ELSE
RAISE EXCEPTION ''Impossible to delete source because a target record
with content already exists.'';
-- cancel transaction. Record will not be deleted.
END IF;

RETURN old;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER "tg_data_source_d" AFTER DELETE ON
"translationforge_data_source" FOR EACH ROW EXECUTE PROCEDURE
tg_data_source_d();
COMMENT ON TRIGGER "tg_data_source_d" ON "translationforge_data_source" IS '';
**********************

Make sure to use pgAdmin2 latest CVS version to write functions. To get the
latest CVS binaries, install pgAdmin2 stable release and upgrade binaries
from binaries folder in CVS.

pgAdmin2 latest version offers pseudo trigger and view modification feature.
This will allow you to alter a trigger you just wrote...

Best regards,
Jean-Michel POURE