Re: Foreign key not working in some cases when using triggers

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> If possible, can you give a complete example script that shows the
> problem?

Here it is.

CREATE OR REPLACE FUNCTION qmail_account_check() RETURNS TRIGGER AS '
-- do some check before insert/update on qmail_account
DECLARE
rec1 RECORD;
rec2 RECORD;
rec3 RECORD;
recx RECORD;
BEGIN
-- allow the script only before operation on qmail_account
IF NOT (TG_WHEN = ''BEFORE'' AND TG_RELNAME = ''qmail_account'') THEN
RAISE NOTICE ''qmail_account_check RETURNS NULL 1 for % @ %'',
NEW.username,NEW.domain;
RETURN NULL;
END IF;
-- INSERT, UPDATE
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
IF NEW.home ISNULL OR NEW.home = '''' THEN
-- get qmail base dir from table constants
NEW.home := get_constant(''qmailhome'');
IF NEW.home ISNULL THEN
RAISE EXCEPTION ''QMAIL home dir lookup failed on table constants'';
END IF;
SELECT INTO rec1 public_mail FROM zone WHERE name = NEW.domain AND
public_mail;
IF FOUND THEN -- public domain, create structured home dir
-- NEW.home/NEW.domain/u/us/username
RAISE NOTICE ''Zone % is public'', NEW.domain;
SELECT INTO rec2 NEW.home || ''/'' ||
btrim(NEW.domain,'' '')::varchar || ''/'' ||
substring(NEW.username from 1 for 1) ||
''/'' ||
substring(NEW.username from 1 for 2) ||
''/'' || NEW.username AS home;
NEW.home := rec2.home;
ELSE
-- private zone, set homedir to NEW.home/NEW.domain/username
RAISE NOTICE ''Zone % is private'', NEW.domain;
SELECT INTO rec3 NEW.home || ''/'' ||
btrim(NEW.domain,'' '')::varchar || ''/'' ||
NEW.username AS home;
NEW.home := rec3.home;
END IF;
END IF;
IF NEW.password ISNULL OR NEW.password = '''' THEN
NEW.password := generate_password();
END IF;
RETURN NEW;
END IF;
RAISE NOTICE ''qmail_account_check RETURNS NULL 2 for % @ %'',
NEW.username,NEW.domain;
RETURN NULL;
END
' LANGUAGE 'plpgsql';

- --

Martin Edlman
Fortech s.r.o, Litomysl
Public PGP key: http://edas.visaci.cz/#keys
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/WCDmoFlEKJy9loQRAhzLAJ0X5y+bbhG3MVih0acZvzuqGJ9KzgCfftZ5
DsPMUXkkwX1heP7VdN3aTro=
=YIt6
-----END PGP SIGNATURE-----