cmdtrigger.ext.sql

BEGIN;

SET LOCAL client_min_messages TO notice;

CREATE OR REPLACE FUNCTION extension_whitelist
 (
   IN cmd_string     text,
   IN cmd_nodestring text,
   IN schemaname     text,
   IN extname        text
 )
 RETURNS void
 LANGUAGE plpgsql
AS $$
BEGIN
  -- RAISE NOTICE 'cmd:  %', cmd_string;
  -- RAISE NOTICE 'tree: %', cmd_nodestring;

  IF extname NOT IN ('hstore', 'ip4r')
  THEN
    -- RAISE EXCEPTION would "kill" current transaction
    RAISE WARNING 'Not allowed to install extension %', extname;
  ELSE
    EXECUTE 'ALTER TRIGGER whitelist ON COMMAND CREATE EXTENSION SET DISABLE';
    EXECUTE 'CREATE EXTENSION ' || extname;
    EXECUTE 'ALTER TRIGGER whitelist ON COMMAND CREATE EXTENSION SET ENABLE ALWAYS';
  END IF;
END;
$$;

CREATE TRIGGER whitelist_foo
    INSTEAD OF COMMAND CREATE EXTENSION
       EXECUTE PROCEDURE extension_whitelist();

ALTER TRIGGER whitelist_foo ON COMMAND CREATE EXTENSION RENAME TO whitelist;

SELECT oid, *, pg_describe_object('pg_cmdtrigger'::regclass, oid, 0)
  FROM pg_cmdtrigger;

CREATE EXTENSION hstore;
CREATE EXTENSION cube;

SELECT oid, *, pg_describe_object('pg_cmdtrigger'::regclass, oid, 0)
  FROM pg_cmdtrigger;

ROLLBACK;