下面是我的脚本:
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 line:
CREATE OR REPLACE TRIGGER {triggerName}
,
创建或替换触发器{accountId}.{triggerName}
不能工作,但是
创建或替换触发器"{accountId}.{triggerName}">
将工作,我不明白为什么。
其他所有内容前面都有模式名,但触发器不需要它。
,如果我不放它,它仍然在正确的模式中创建。是因为它与创建模式指令在同一执行期间吗?
引自手册
名称不能是模式限定的-触发器继承其表的模式