I have a DB (DB1) in postgres 9.4 who use DBLINK to query another DB (DB2), and vice versa, using views and functions.

But now i have to add 2 schemes to DB1 (CLIENT1 And CLIENT2) and i want when i make a query from DB1 to DB2 use the PUBLIC schema, eg: DB1.CLIENT1 to DB2.public

Also when i make a query from DB2 to DB1 use the scheme being used in DB1, eg: DB2.public to DB1.CLIENT1

DB1 to DB2

My problem is i have inside the views and functions the scheme used and i don't know how to change it to use specify the schema used in DB1 from DB2 and vice versa.

i append a example of the view used in each DB:

View DB1 to DB2:

CREATE OR REPLACE VIEW "cliente1"."datos_hacienda_bovina" AS 
 SELECT t1.tropa,
   FROM cliente1.dblink(( SELECT ((((((('port=5432 host='::text || (configuracion_trazabilidad.config_servidor)::text) || ' dbname='::text) || (configuracion_trazabilidad.config_base_datos)::text) || ' user='::text) || (configuracion_trazabilidad.config_usuario)::text) || ' password='::text) || (configuracion_trazabilidad.config_clave)::text) AS configuracion
           FROM cliente1.configuracion_trazabilidad
          WHERE (configuracion_trazabilidad.config_tipo_stock = 0)
         OFFSET 0
         LIMIT 1), 'SELECT
ingreso_hacienda.inghac_tropa AS tropa,
to_char(ingreso_hacienda.inghac_fecha, ''yyyy'') AS anio,
ingreso_hacienda.inghac_peso_promedio AS peso_promedio,
clasificacion_animales.clasani_sigla AS clasificacion,
ingreso_hacienda_detalle.inghacd_cantidad AS cabezas_clasificacion,
(ingreso_hacienda.inghac_numero_cabezas - ingreso_hacienda.inghac_numero_caidos - ingreso_hacienda.inghac_numero_muertos) AS cabezas, 
0 AS tipo, 
procedencias.proc_codigo AS codigo_procedencia,
procedencias.proc_nombre AS procedencia
LEFT JOIN public.ingreso_hacienda_detalle ON ingreso_hacienda_detalle.inghac_id = ingreso_hacienda.inghac_id
LEFT JOIN public.clasificacion_animales ON ingreso_hacienda_detalle.clasani_id = clasificacion_animales.clasani_id
LEFT JOIN public.procedencias ON procedencias.proc_codigo = ingreso_hacienda.inghac_procedencia
ORDER BY inghac_fecha, inghac_tropa'::text) t1(tropa integer, anio text, peso_promedio numeric, clasificacion text, cabezas_clasificacion integer, cabezas integer, tipo integer, codigo_procedencia text, procedencia text);;

View DB2 to DB1:

CREATE OR REPLACE VIEW "public"."articulos_tipo" AS 
 SELECT t1.artti_id,
   FROM dblink(( SELECT ((((((('port=5432 host='::text || (configuracion_trazabilidad.config_servidor)::text) || ' dbname='::text) || (configuracion_trazabilidad.config_base_datos)::text) || ' user='::text) || (configuracion_trazabilidad.config_usuario)::text) || ' password='::text) || (configuracion_trazabilidad.config_clave)::text) AS configuracion
           FROM configuracion_trazabilidad
          WHERE (configuracion_trazabilidad.config_tipo_stock = 0)
         OFFSET 0
         LIMIT 1), 'SELECT clasani_id, clasani_codigo, clasani_nombre FROM "cliente1".clasificacion_animales'::text) t1(artti_id integer, artti_codigo text, artti_nombre text);;

I tried creating two empty schemes in DB2 and then taking the schema name in the DB1 views but does not help me to query from DB2 to DB1.

1- What would be the best way to work from a DB with schemes and other without schemes?

2- What changes should I do?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.