CODEWITHSUNDEEP
postgresqlfunctiontriggersdrop
NelsonC
NelsonC

Reputation: 63

Automatically DROP FUNCTION when DROP TABLE on POSTGRESQL 11.7

I'm trying to, somehow, trigger a automatic function drop when a table is dropped and I can't figure out how to do it.

TL;DR: Is there a way to trigger a function drop when a specific table is dropped? (POSTGRESQL 11.7)

Detailed explanation

I'll try to explain my problem using a simplified use case with dummy names.

  1. I have three tables: sensor1, sensor2 and sumSensors;

  2. A FUNCTION (sumdata) was created to INSERT data on sumSensors table. Inside this function I'll fetch data from sensor1 and sensor2 tables and insert its sum on table sumSensors;

  3. A trigger was created for each sensor table which like this:

    CREATE TRIGGER trig1 AFTER INSERT ON sensor1 FOR EACH ROW EXECUTE FUNCTION sumdata();

  4. Now, when a new row is inserted on tables sensor1 OR sensor2, the function sumdata will be executed and insert the sum of last values from both on table sumSensors

If I wanted to DROP FUNTION sumdata CASCADE;, the triggers would be automatically removed from tables sensor1 and sensor2. Until now that's everything fine! But that's not what I want.

My problem is:

Q: And if I just DROP TABLE sumSensors CASCADE;? What would happen to the function which was meant to insert on this table?

A: As expected, since there's no association between sumSensors table and sumdata function, the function won't be dropped (still exist)! The same happens to the triggers which use it (still exist). This means that when a new row is inserted on sensor tables, the function sumdata will be executed and corrupted, leading to a failure (even the INSERT which triggered the function execution won't be actually inserted).

Is there a way to trigger a function drop when a specific table is dropped?

Thank you in advance

Upvotes: 3

Views: 1720

Answers (1)

filiprem
filiprem

Reputation: 7144

There is no dependency tracking for functions in PostgreSQL (as of version 12).

You can use event triggers to maintain the dependencies yourself.

Full example follows.

More information: documentation of event triggers feature, support functions.

BEGIN;

CREATE TABLE _testtable ( id serial primary key, payload text );

INSERT INTO _testtable (payload) VALUES ('Test data');

CREATE FUNCTION _testfunc(integer) RETURNS integer
LANGUAGE SQL AS $$ SELECT $1 + count(*)::integer FROM _testtable; $$;

SELECT _testfunc(100);

CREATE FUNCTION trg_drop_dependent_functions()
RETURNS event_trigger 
LANGUAGE plpgsql AS $$
DECLARE
    _dropped record;
BEGIN
    FOR _dropped IN
        SELECT schema_name, object_name
        FROM pg_catalog.pg_event_trigger_dropped_objects()
        WHERE object_type = 'table'
    LOOP
        IF _dropped.schema_name = 'public' AND _dropped.object_name = '_testtable' THEN
            EXECUTE 'DROP FUNCTION IF EXISTS _testfunc(integer)';
        END IF;
    END LOOP;
END;
$$;

CREATE EVENT TRIGGER trg_drop_dependent_functions ON sql_drop
EXECUTE FUNCTION trg_drop_dependent_functions();

DROP TABLE _testtable;

ROLLBACK;

Upvotes: 4

Related Questions