使用dblink复制新数据的函数中的语法错误 [英] Syntax error in function using dblink to replicate new data

问题描述

我从来没有在Postgres中创建过一个函数，我跟着一些教程并编写了这段代码，但我不知道它为什么错了，控制台中的错误是：

 语法错误处于或接近SELECT
行5：SELECT public.dblink_connect（'hostaddr = 127.0.0.1 port = 54。 .. 
  

我在Ubuntu上使用9.3.6版本。
$ b $ pre $ 创建或替换函数fn_replicate_insertof_students（）
返回文本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 ，街道，供应商，城市，邮编，country_id，电子邮件，电话，日期，客户，手机，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，假的，NEW.city，NEW.zip，NEW.country_id，NEW.email，NEW.phone，NEW.date，真实，NEW.mobile，NEW.ref，NEW.state_id，假的，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挥发性安全定义
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（）;


解决方案

@尼克提出了一些优点，但还有更多：

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

• search_path如何影响标识符解析和当前模式

更重要的是，整个功能是 以目前的形式 。你打开一个dblink连接，但你不使用它。看起来你想抛出 dblink_exec（）。但是，由于 NEW 在另一方不可见，所以您需要先连接查询字符串与值为 NEW 的值虫洞。所以你有自己的动态SQL的一个很好的例子。

详细的代码示例和dblink函数的解释在dba.SE这个最近的相关答案中：

• 持久性插入UDF，即使函数中止

另外，这必须是触发器功能用于触发器。

您的功能可以像这样工作：

 创建或替换函数fn_replicate_insertof_students（）
 RETURNS 触发器 AS 
 $ func $ 
 BEGIN 
  PERFORM  public.dblink_connect（'hostaddr = 127.0.0.1 port = 5433 
 dbname = Utiles user = postgres password = Mypass'）; 
 
  PERFORM public.dblink_exec（格式（
 $ f $ INSERT INTO res_partner（company_id，name，lang，comment，...）
 VALUES（1，％ L，'es_MX'，％L，...）$ f $ 
，NEW.name，NEW.comment，...））;  
 
  PERFORM  public.dblink_disconnect（）; 
 
  RETURN NULL;   - 仅适用于AFTER触发器
 
 END 
 $ func $ LANGUAGE  plpgsql  VOLATILE SECURITY DEFINER 
 SET search_path = myschema，pg_temp; 
 ALTER FUNCTION fn_replicate_insertof_students（）OWNER TO postgres; - 猜测
  

还要确保将所有者设置为 SECURITY DEFINER function a>。

考虑使用 FOREIGN SERVER ， USER MAPPING 和目标服务器上的密码文件。详情请参阅上述链接。

整个想法是一个相当昂贵的复制特例。对于某些插入表格，这没问题，但有更好的解决方案大量加载。

I never created a function in Postgres, I followed some tutorials and made this code, but I don't know why it is wrong, the error in the console is:

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

I'm using the version 9.3.6 on Ubuntu.

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();

解决方案

@Nick raised some good points, but there's more:

Your search_path is actually done right. pg_catalog is automatically included first unless you explicitly put it in there on a different position.

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

More importantly, the whole function is nonsense in its current form. You open a dblink connection, but you don't use it. Looks like you want to throw in dblink_exec(). But you need to concatenate the query string with values form NEW first, since NEW is not visible on the other side of the wormhole. So you have yourself a nice example of dynamic SQL. Quite a steep start for a beginner!

Detailed code example and explanation for function with dblink in this recent related answer on dba.SE:

• Persistant inserts in a UDF even if the function aborts

Also, this must be a trigger function to be used in a trigger.
Your function could work like this:

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

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, ... ));

PERFORM public.dblink_disconnect();

RETURN NULL;  -- only ok for AFTER trigger

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

Also make sure to set the owner right for a SECURITY DEFINER function.

Consider using a FOREIGN SERVER, USER MAPPING and a password file on the target server. Details in above link.

The whole idea is a rather expensive special case of replication. For some inserts to a table, this is ok, but there are better solutions for massive load.

这篇关于使用dblink复制新数据的函数中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！