postgresql - Syntax error in function using dblink to replicate new data -


i never created function in postgres, followed tutorials , made code, don't know why wrong, error in console is:

"syntax error @ or near "select" line 5:     select public.dblink_connect('hostaddr=127.0.0.1 port=54...

i'm using version 9.3.6 on ubuntu.

create or replace function fn_replicate_insertof_students()       returns text     $body$     begin     select public.dblink_connect('hostaddr=127.0.0.1 port=5433 dbname=utiles user=postgres password=mypass');  insert 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   each row   execute procedure fn_replicate_insertof_students();

@nick raised points, there's more:

your search_path done right. pg_catalog automatically included first unless explicitly put in there on different position.

more importantly, whole function nonsense in current form. open dblink connection, don't use it. looks want throw in dblink_exec(). need concatenate query string values form new first, since new not visible on other side of wormhole. have nice example of dynamic sql. quite steep start beginner!

detailed code example , explanation function dblink in recent related answer on dba.se:

also, must trigger function used in trigger.
function work this:

create or replace function fn_replicate_insertof_students()   returns trigger $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 res_partner (company_id, name, lang, comment, ... )       values  (1, %l, 'es_mx', %l, ... )$f$     , new.name, new.comment, ... ));  perform public.dblink_disconnect();  return null;  -- ok after trigger  end $func$ language plpgsql volatile security definer                         set search_path=myschema, pg_temp; alter function fn_replicate_insertof_students() owner postgres;  -- guessing

also make sure set owner right security definer function.

consider using foreign server, user mapping , password file on target server. details in above link.

the whole idea rather expensive special case of replication. inserts table, ok, there better solutions massive load.


-

Comments

Post a Comment

Popular posts from this blog

OpenCV OpenCL: Convert Mat to Bitmap in JNI Layer for Android -

there several posts converting mat bitmap using utils.mattobitmap() function. i'm assuming function can called in java layer after importing utils class. i want transfer data memory address pointed uint32_t* bmpcontent; in code below. jniexport void jnicall java_com_nod_nodcv_nodcvactivity_runfilter( jnienv *env, jclass clazz, jobject outbmp, jbytearray indata, jint width, jint height, jint choice, jint filter) { int outsz = width*height; int insz = outsz + outsz/2; androidbitmapinfo bmpinfo; if (androidbitmap_getinfo(env, outbmp, &bmpinfo) < 0) { throwjavaexception(env,"gaussianblur","error retrieving bitmap meta data"); return; } if (bmpinfo.format != android_bitmap_format_rgba_8888) { throwjavaexception(env,"gaussianblur","expecting rgba_8888 format"); return; } uint32_t* bmpcontent; if (androidbitmap_lockpixels(env, outbmp,(void**)&bmpcontent)...
Read more

android - org.xmlpull.v1.XmlPullParserException: expected: START_TAG {http://schemas.xmlsoap.org/soap/envelope/}Envelope -

i trying hit soap api in application login purpose , getting above mentioned error , can 1 tell me going wrong , first time soap apis. here code hit soap :- vector<object> args = new vector<object>(); hashtable<string, object> hashtable = new hashtable<string, object>(); hashtable.put("email", "mss.siddhart28@gmail.com"); hashtable.put("password", "siddharth"); args.add(hashtable); soapserializationenvelope envelope = new soapserializationenvelope( soapenvelope.ver11); envelope.dotnet = true; envelope.xsd = soapserializationenvelope.xsd; envelope.enc = soapserializationenvelope.enc; (new marshalhashtable()).register(envelope); httptransportse androidhttptransport = new httptransportse( "http://tempuri.org/", 15000); androidhttptransport.debug = true; string result = ""; try { soapobject request = new soapobject("http:...
Read more

python - How to remove the Xframe Options header in django? -

i have made page has iframe . inside iframe want show multiple different links article facebook, or news, or youtube video or other possible url. but, due xframe header, unable so. referred following link: https://docs.djangoproject.com/en/1.8/ref/clickjacking/ , django xframeoptionsmiddleware (x-frame-options) - allow iframe client ip but didn't help. my settings.py file's middleware_classes is: middleware_classes = ( 'django.contrib.sessions.middleware.sessionmiddleware', 'django.middleware.common.commonmiddleware', 'django.middleware.csrf.csrfviewmiddleware', 'django.contrib.auth.middleware.authenticationmiddleware', 'django.contrib.auth.middleware.sessionauthenticationmiddleware', 'django.contrib.messages.middleware.messagemiddleware', 'django.middleware.clickjacking.xframeoptionsmiddleware', ) from http://django-secure.readthedocs.org/en/latest/middleware.html , found using ...
Read more