here is my script:

CREATE SCHEMA IF NOT EXISTS {accountId};
CREATE TABLE IF NOT EXISTS {accountId}.{tableCommandsName}
(
    id              int         GENERATED ALWAYS AS IDENTITY,
    ts              timestamp   WITHOUT TIME ZONE NOT NULL,
    command         varchar     NOT NULL,
    ts_executed     timestamp   WITHOUT TIME ZONE,
    output          varchar
);

CREATE INDEX IF NOT EXISTS idx_commands ON {accountId}.{tableCommandsName} (ts_executed) WHERE ts_executed IS NULL;

CREATE OR REPLACE VIEW {accountId}.pending_commands AS
    SELECT id, ts, command from {accountId}.{tableCommandsName} WHERE ts_executed IS NULL ORDER BY ts ASC;

CREATE OR REPLACE FUNCTION {accountId}.on_commands_change ()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
    DECLARE
      notification JSON;
    BEGIN
        notification = json_build_object(
            'table',TG_TABLE_NAME,
            'data', row_to_json(NEW));

        PERFORM pg_notify('datachange', notification::TEXT);
        RETURN NEW;
    END
$BODY$;

CREATE OR REPLACE TRIGGER {triggerName}
    AFTER INSERT ON {accountId}.{tableCommandsName}
    FOR EACH ROW EXECUTE PROCEDURE {accountId}.on_commands_change();

my focus is the line:

CREATE OR REPLACE TRIGGER {triggerName}

where

CREATE OR REPLACE TRIGGER {accountId}.{triggerName}

will not work, but

CREATE OR REPLACE TRIGGER "{accountId}.{triggerName}"

will work, and I don't understand why.

everything else has the schema name in front, but the trigger doesn't want it.

and if I don't put it, it's still created in the right schema. Is it because it's during the same execution as the create schema instruction?

CodePudding user response：

Quote from the manual

The name cannot be schema-qualified — the trigger inherits the schema of its table