Re: no "ON INSERT ignore" in postgre?
От | Jean-Michel POURE |
---|---|
Тема | Re: no "ON INSERT ignore" in postgre? |
Дата | |
Msg-id | 200204050257.g352vDGB011157@www1.translationforge обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: