Re: MDB - Delete LOB

"Manuel Lemos" <mlemos@acm.org> wrote in message news:20030609131231.55610.qmail@pb1.pair.com... > Hello, > > Yes, I have been thinking about a trigger solution too for a while. > Although I have not tried it, I think it may be the only viable > solution. I am not sure of PostgreSQL procedure language has all that is > necessary but it may also solve not only the delete rows with lobs > problem but also the update rows with lobs. > > I just think it is much more complicated than you think because tables > may have more than one lob column and each of these columns may have nulls. > Yes, you are right! The generale case does not fit with the trigger I wrote, but my purpose was not do write the ultimate trigger; I wanted just to depict a possible way... The trigger could be easily extended to the situation you mention (multiple oid column, null oid management, etc). For the best of my knowledge, the plgsql language has all the necessary features. Probably, something like this <SQL> CREATE FUNCTION delete_oid() RETURNS TRIGGER AS ' BEGIN IF OLD.your_oid_first_field IS NOT NULL THEN DELETE FROM pg_largeobject where loid = OLD.your_oid_first_field; END IF; IF OLD.your_oid_second_field IS NOT NULL THEN DELETE FROM pg_largeobject where loid = OLD.your_oid_second_field; END IF; RETURN NULL; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER delete_oid AFTER DELETE ON your_table FOR EACH ROW EXECUTE PROCEDURE delete_oid(); </SQL> The UPDATE case is specular. You need another trigger BEFORE UPDATE and a update_oid() function the check if the NEW.your_oid_field is null, otherwise update the pg_pargeobject table... but I dont't tested it yet. Maybe there is a more general or more efficent way to implement this, but this is the output of my best effort! I stop here because I don't want to go off-topic! Regards Davide -- PJ