Syntax error in function using dblink to replicate new data

Question

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

What is the exact error you get? any error code or complete error description would help — JavaHopper
– JavaHopper, Commented Jul 1, 2015 at 23:19
It's procedural code, so the language should be plpgsql and not sql. — Daniel Vérité
– Daniel Vérité, Commented Jul 1, 2015 at 23:38
Cross-posted to dba.stackexchange.com/q/105723/7788 apparently. Sigh. — Craig Ringer
– Craig Ringer, Commented Jul 2, 2015 at 0:14

Community · Accepted Answer · 2017-05-23 12:32:32Z

@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.

Oops... Thanks, not sure why I thought the search_path change was necessary.

Nick Barnes · Accepted Answer · 2015-07-02 01:29:31Z

2

You have marked the function as LANGUAGE sql, which is only for functions containing single SQL statements. This function is written in LANGUAGE plpgsql.

It will still complain about the SELECT, because plpgsql requires that you actually do something with the result of a SELECT. You'll need to replace SELECT with PERFORM, to make it explicit that you're discarding the result.

edited Jul 2, 2015 at 1:29

answered Jul 1, 2015 at 23:26

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Collectives™ on Stack Overflow

Syntax error in function using dblink to replicate new data

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related