写入触发功能时DB链路查询错误

Question

我正在使用PSQL 9.6，并试图写一个触发器。现在的问题是，我正面临以下查询中的错误，并且终端输出不够详细，无法确定我在此查询中出错的地方。

函数是这样的:

CREATE OR REPLACE FUNCTION insert_dblink_func()
RETURNS trigger AS
$BODY$
BEGIN
perform dblink_exec("insert into Some_Table select * from dblink( 'host=SOMEPRODSERVER dbname=DBNAME user=USERNAME password=PASSWORD', 'select id,name,helpline,email,created_at,updated_at') as sourceTable (id integer,name character varying,helpline character varying,email character varying,created_at timestamp with time zone,updated_at timestamp with time zone) on conflict(id) do update set id=excluded.id,name=excluded.name,helpline=excluded.helpline,email=excluded.email,created_at=excluded.created_at,updated_at=excluded.updated_at");
perform dblink_disconnect();
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql;

当我执行这个函数时，我得到的输出是这个

LINE 5:   perform dblink_exec("insert into TABLENAME sel...
^
CREATE FUNCTION

所以我对错误可能是什么感到困惑。TIA

Answer 1

我很困惑，因为我试过把你的dblink查询分成小步骤，我看到在选择语法中有一些问题(+使用双引号而不是单引号):

INSERT INTO
some_table
SELECT *
FROM DBLINK('host=SOMEPRODSERVER dbname=DBNAME user=USERNAME password=PASSWORD',
'SELECT id,name,helpline,email,created_at,updated_at') --<< here's not table with "FROM statement"
AS sourcetable (id INTEGER,
name CHARACTER VARYING,
helpline CHARACTER VARYING,
email CHARACTER VARYING,
created_at TIMESTAMP WITH TIME ZONE,
updated_at TIMESTAMP WITH TIME ZONE)
ON CONFLICT(id) DO UPDATE SET
id=excluded.id,
name=excluded.name,
helpline=excluded.helpline,
email=excluded.email,
created_at=excluded.created_at,
updated_at=excluded.updated_at

而且，根据描述，不清楚这个触发器应该触发什么动作。您能否将代码分成小步骤并提供更多信息?下面是一个dbfiddle示例，您可以使用数据

对其进行改进。