postgresql - 使用 dblink 复制新数据的函数中出现语法错误

Question

我从来没有在 Postgres 中创建过函数，我按照一些教程编写了这段代码，但我不知道为什么会出错，控制台中的错误是:

"syntax error at or near "SELECT"
LINE 5:     SELECT public.dblink_connect('hostaddr=127.0.0.1 port=54...

我在 Ubuntu 上使用的是 9.3.6 版本。

CREATE OR REPLACE FUNCTION fn_replicate_insertof_students()
      RETURNS text AS
    $BODY$
    BEGIN
    SELECT public.dblink_connect('hostaddr=127.0.0.1 port=5433 dbname=Utiles user=postgres password=Mypass');

INSERT INTO res_partner (company_id,name,lang,comment,street,supplier,city,zip,country_id,email,phone,date,customer,mobile,ref,state_id,opt_out,city_id,l10n_mx_city2,l10n_mx_street3,l10n_mx_street4,notification_email_send,type,street2,active)
                        VALUES  (1,NEW.name,'es_MX',NEW.comment,NEW.street,false,NEW.city,NEW.zip,NEW.country_id,NEW.email,NEW.phone,NEW.date,true,NEW.mobile,NEW.ref,NEW.state_id,false,NEW.city_id,NEW.l10n_mx_city2,NEW.l10n_mx_street3,NEW.l10n_mx_street4,NEW.notification_email_send,NEW.type,NEW.street2,NEW.active));

SELECT public.dblink_disconnect();

END;
$BODY$ LANGUAGE sql VOLATILE SECURITY DEFINER
SET search_path=myschema, pg_temp;


CREATE TRIGGER tr_replicate_insertof_students
  AFTER INSERT 
  ON res_partner
  FOR EACH ROW
  EXECUTE PROCEDURE fn_replicate_insertof_students();

Answer 1

@Nick raised some good points , 但还有更多:

您的search_path 实际上正确。 pg_catalog 会自动包含在第一个 中，除非您明确将它放在其他位置。

• How does the search_path influence identifier resolution and the "current schema"

更重要的是，整个函数当前形式毫无意义。您打开了一个 dblink 连接，但您没有使用它。看起来您想加入 dblink_exec()。但是您需要先将查询字符串与 NEW 的值连接起来，因为 NEW 在虫洞的另一边是不可见的。因此，您自己就有了一个很好的动态 SQL 示例。对于初学者来说，这是一个相当陡峭的开始!

dba.SE 最近的相关回答中详细的代码示例和 dblink 函数的解释:

• Persistant inserts in a UDF even if the function aborts

此外，这必须是 trigger function在触发器中使用。
您的函数可以像这样工作:

CREATE OR REPLACE FUNCTION fn_replicate_insertof_students()
  RETURNS <b>trigger</b> AS
$func$
BEGIN
<b>PERFORM</b> public.dblink_connect('hostaddr=127.0.0.1 port=5433 
                dbname=Utiles user=postgres password=Mypass');

<b>PERFORM public.dblink_exec(format(
   $f$INSERT INTO res_partner (company_id, name, lang, comment, ... )
      VALUES  (1, %L, 'es_MX', %L, ... )$f$
    , NEW.name, NEW.comment, ... ));</b>

<b>PERFORM</b> public.dblink_disconnect();

<b>RETURN NULL;</b>  -- only ok for AFTER trigger

END
$func$ LANGUAGE <b>plpgsql</b> VOLATILE SECURITY DEFINER
                        SET search_path=myschema, pg_temp;
ALTER FUNCTION fn_replicate_insertof_students() OWNER TO postgres;  -- guessing

还要确保为 SECURITY DEFINER function 设置所有者权限.

考虑在目标服务器上使用FOREIGN SERVER、USER MAPPING 和密码文件。详见above link .

整个想法是一个相当昂贵的复制特例。对于表的一些插入，这没问题，但是有 better solutions for massive load .