INSERT IN REMOTE TABLE FROM LOCAL POSTGRESQL TABLE

1

using postgresql, the following instruction updates a local table from a remote table:

INSERT INTO tblA
    SELECT id, time 
    FROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB')
    AS t(id integer, time integer)
    WHERE time > 1000;

Now, I need the opposite, from a local table, update a table that is in a hosting. The database is in postgresql. Could someone help me with an example or tell me how to do it? Thank you in advance for your cooperation.

Toledano, I attach the entire function to better understand what I need to do. The table to which I must make the insert, is in a hosting

SELECT dblink_connect('miconexion'
       ,'hostaddr=185.15.999.9 port=11076 dbname=axio user=webadmin password=xxXXxxXX');

CREATE OR REPLACE FUNCTION edgar() RETURNS SETOF tabxter AS
$BODY$
DECLARE
    nit varchar;
    nom varchar;
    reg tabxter%rowtype;
BEGIN
    RAISE NOTICE 'mensaje--------------------------';
    FOR reg IN
       SELECT "TERCXNIT","TERCNOMB" FROM public.tabxter a
    LOOP
      RETURN NEXT reg; -- return current row of SELECT
      nit  = reg."TERCXNIT";
      Xnit = reg."TERCXNIT";
      nom  = reg."TERCNOMB";
      RAISE NOTICE 'mensaje 9 %,%',nit,reg."TERCNOMB";
      PERFORM dblink_exec('miconexion','***INSERT INTO public.tabxter("TERCXNIT") VALUES(nit)***');
    END LOOP;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM edgar();

EQUALLY ATTACHED FUNCTION SCREENING for better appreciation

    
asked by soporte 11.05.2017 в 15:33
source

1 answer

1

You have to make the necessary queries to obtain the data you want to register and concatenate them to the query of your db link

select dblink('host=xxx.xxx.xx.xx dbname=database user=postgres password=postgres port=5432 ', 'insert into tp_table (field) values(''||(select field from tp_table_local where id=1)||'')') 
    
answered by 27.03.2018 в 23:35