Postgres Trigger-Based Insert Redirection Without Breaking Returning

Postgres trigger-based insert redirection without breaking RETURNING

The only workaround I found, is to create a view for the base table & use INSTEAD OF triggers on that view:

CREATE TABLE flags_base (
    id integer NOT NULL,
    flaggable_type character varying(255) NOT NULL,
    flaggable_id integer NOT NULL,
    body text
);

ALTER TABLE ONLY flags_base
    ADD CONSTRAINT flags_base_pkey PRIMARY KEY (id);

CREATE TABLE "comment_flags" (
 CHECK ("flaggable_type" = 'Comment'),
 PRIMARY KEY ("id")
) INHERITS ("flags_base");

CREATE TABLE "profile_flags" (
 CHECK ("flaggable_type" = 'Profile'),
 PRIMARY KEY ("id")
) INHERITS ("flags_base");

CREATE OR REPLACE VIEW flags AS SELECT * FROM flags_base;

CREATE OR REPLACE FUNCTION flag_insert_trigger_fun() RETURNS TRIGGER AS $BODY$
BEGIN
  IF (NEW."flaggable_type" = 'Comment') THEN
    INSERT INTO comment_flags VALUES (NEW.*);
  ELSIF (NEW."flaggable_type" = 'Profile') THEN
    INSERT INTO profile_flags VALUES (NEW.*);
  ELSE
    RAISE EXCEPTION 'Wrong "flaggable_type"="%", fix flag_insert_trigger_fun() function', NEW."flaggable_type";
  END IF;
  RETURN NEW;
END; $BODY$ LANGUAGE plpgsql;

CREATE TRIGGER flag_insert_trigger
  INSTEAD OF INSERT ON flags
  FOR EACH ROW EXECUTE PROCEDURE flag_insert_trigger_fun();

But this way you must supply the id field on each insertion (even if flags_base's primary key has a default value / is a serial), so you must prepare your insert trigger to fix NEW.id if it is a NULL.

UPDATE: It seems views' columns can have a default values too, set with

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression

which is only used in views have an insert/update rule/trigger.

http://www.postgresql.org/docs/9.3/static/sql-alterview.html

How to create a trigger on after insert without returning NEW in postgres?

The trigger function is required to return something because its definition does not define that it is used as an after trigger. That is only known in the trigger definition.

Adding a RETURN NEW; statement might be a good idea to prevent surprises when it is used (accidentally) as a BEFORE trigger in the future. You can also choose to RETURN NULL; as the result is ignored for AFTER triggers.

RETURNING clause with BEFORE INSERT trigger

Good question. That is typical problem with partitioning. I'm afraid there is no good, or elegant solution, and all you can do is to introduce some workarounds:

• inserting, and then deleting - yes, far from perfect,
• if you need id generated by serial type, you can use currval()... That would mean another query.

Here there is yet another other way - you can create view, and use instead of trigger for that view. It is hard to tell if that is elegant, but for me that is quite close to that.

PostgreSQL trigger on insert external script with arguments

As documented in the manual

The trigger function must be declared as a function taking no arguments and returning type trigger.

Your function is neither declared as returns trigger nor does it have "no arguments".

You need to create a trigger function that calls your function:

create function foo_trigger_func()
  returns trigger
as
$$
begin
  perform foo3(old.ip, old.username);
end;
$$
language plpgsql;

Then you create a trigger using that function:

CREATE TRIGGER t_foo
  BEFORE UPDATE ON mapping
   FOR EACH ROW
   EXECUTE PROCEDURE foo_trigger_func();

Insert trigger ends up inserting duplicate rows in partitioned table

A simpler way is to create stored procedure instead of the triggers, for example add_foo( [parameters] ), which would decide which partition is suitable to insert a row to and return id (or the new record values, including id). For example:

CREATE OR REPLACE FUNCTION add_foo(
    _d_id   INTEGER
,   _label  VARCHAR(4)
) RETURNS BIGINT AS $$
DECLARE
    _rec    foo%ROWTYPE;
BEGIN
    _rec.id := nextval('foo_id_seq');
    _rec.d_id := _d_id;
    _rec.label := _label;
    EXECUTE 'INSERT INTO foo_' || ( _d_id % 2 ) || ' SELECT $1.*' USING _rec;
    RETURN _rec.id;
END $$ LANGUAGE plpgsql;

Insert into view not returning primary key

After the insert in the trigger function and before RETURN NEW;, I added...

NEW."PlaceId" = currval('"tbl_place_PlaceId_seq"');

INSERT ...RETURNING .. comes up empty when BEFORE trigger cancels statement

I wrote the answer you are referring to. As I already hinted over there:

You could also use a RULE ... INSTEAD .. for this purpose.

RULE

Rules can be tricky. I'd rather use triggers where possible. Be sure to read a bit, before you try this:

CREATE OR REPLACE RULE tbl_ins AS
ON INSERT TO tbl
DO INSTEAD
INSERT INTO tbl2 (col1, col2, ...)  -- just do mention columns where ...
VALUES (NEW.col1, NEW.col2, ...)    -- ... you want to insert column defaults
RETURNING tbl2.*

That would return values from tbl2 while avoiding phantom rows. However, per documentation on CREATE RULE:

In a rule for INSERT, UPDATE, or DELETE on a view, you can add a
RETURNING clause that emits the view's columns. This clause will be
used to compute the outputs if the rule is triggered by an
INSERT RETURNING, UPDATE RETURNING, or DELETE RETURNING command respectively.
When the rule is triggered by a command without RETURNING, the rule's
RETURNING clause will be ignored. The current implementation allows
only unconditional INSTEAD rules to contain RETURNING;

Bold emphasis mine.

Since you mention sub-tables, I take it you'd need conditions to distribute the inserts ...

currval() / lastval()

If you operate with a trigger FOR EACH ROW you can easily fetch appropriate values from sequences with currval() / lastval(). The tricky part is to return those values from a trigger function. I can only think of writing to a temporary table. Needs some thinking when to create and when to drop that one ...

I would probably rethink the whole approach and redirect the data to multiple INSERT statements to actual target tables ...

Postgres - Create trigger that can exist in multiple schemas

You can use the TG_TABLE_SCHEMA variable and set_config() with IS_LOCAL = true to accomplish this:

CREATE OR REPLACE FUNCTION validate_client_user_role()
    RETURNS trigger AS
    $BODY$

    DECLARE role_has_client INT;
    DECLARE user_has_client INT;

    BEGIN
        IF NEW.client_id IS NULL THEN
            RAISE EXCEPTION 'client_id cannot be null';
        END IF;

        IF NEW.user_id IS NULL THEN
            RAISE EXCEPTION 'user_id cannot be null';
        END IF;

        IF NEW.role_id IS NULL THEN
            RAISE EXCEPTION 'role_id cannot be null';
        END IF;

        PERFORM set_config('search_path', TG_TABLE_SCHEMA, true);  -- <-- This line

        SELECT COUNT(*)
        INTO role_has_client
        FROM roles
        WHERE id = NEW.role_id
        AND client_id = NEW.client_id;

        SELECT COUNT(*)
        INTO user_has_client
        FROM client_users
        WHERE user_id = NEW.user_id
        AND client_id = NEW.client_id;

        IF role_has_client = 0 THEN
            RAISE EXCEPTION 'Role is not allowed by client';
        END IF;

        IF user_has_client = 0 THEN
            RAISE EXCEPTION 'User is not allowed by client';
        END IF;

        RETURN NEW;
    END
    $BODY$ LANGUAGE plpgsql;

---

Related Topics