Insertar datos en BD externa utilizando dblink

Muy probablemente a lo largo de la Entrega 3 tendrán que realizar inserciones a una base de datos externa utilizando procedimientos. Es debido a esto que hemos decidido crear esta entrada de la wiki la cual busca presentar una forma de realizar esto utilizando dblink.

Desarrollando el código paso a paso

Cuando uno trabaja con dblink en una base de datos externa y desea realizar operaciones para poder modificar dicha base de datos lo primero que debe hacer es establecer una conexión estable, para esto se utiliza la siguiente línea de código:

PERFORM dblink_connect('db2', 'dbname=XX user=XX password=XXX');

Una vez establecida la conexión debemos ver una forma de realizar nuestra consulta en la base de datos objetivo, pero para poder hacerlo no basta con hacer algo como esto:

INSERT INTO Table VALUES (col_1, col_2, col_3);

Pues al hacer algo de este estilo estamos modificando la tabla Table ubicada al interior de la base de datos que posee en su interior el procedimiento almacenado que estamos ejecutando. Para poder ejecutarlo en la base de datos correcta debemos preparar una query de sql y ejecutarla en la base de datos correspondiente, algo que se muestra a continuación:

insert_statement = 'INSERT INTO Table VALUES ('||col_1||','''||col_2||''', ''col_3'');';
res := dblink_exec('db2', insert_statement, true); -- Aquí ejecutamos la línea anterior en la nueva base de datos
RAISE INFO '%', res; -- En caso de error esto nos muestra la información del problema 
perform dblink_disconnect('db2'); -- Cerramos la conexión

Notar que la variable col_1 corresponde a una variable del tipo entero y posee el formato '||dato||'. Por otro lado, la variable col_2 corresponde a un varchar y posee el formato '''||dato||'''. Notar que col3 corresponde a un varchar fijo.

A partir de todo lo anterior podemos construir el siguiente código

CREATE OR REPLACE FUNCTION example_function (input_1 int, input_2 varchar)
RETURNS void AS
$$
DECLARE -- Declaramos las variables que utilizaremos más adelante
    insert_statement TEXT;
    res TEXT;
BEGIN
    PERFORM dblink_connect('db2', 'dbname=XX user=XX password=XXX'); -- Establecemos la conexión a la otra bdd
    insert_statement = 'INSERT INTO Table VALUES ('||input_1||','''||input_2||''', ''hello world'');';
    res := dblink_exec('db2', insert_statement, true); -- Aquí ejecutamos la línea anterior en la nueva base de datos
    RAISE INFO '%', res; -- En caso de error esto nos muestra la información del problema 
    perform dblink_disconnect('db2'); -- Cerramos la conexión
END
$$ language plpgsql

Manejo de errores

El problema que existe con lo anterior es que, en el caso de que ocurra una falla en la consulta, la conexión con el nombre db2 puede seguir activa, lo que arrojaría un error. Para poder evitar esto debemos manejar aquellos casos en los que nuestro procedimiento almacenado falle e idealmente trataremos de obtener la mayor cantidad de información que nos sea útil para poder identificar y solucionar el error. Con el objetivo de lograr lo solicitado hemos desarrollado el siguiente código:

CREATE OR REPLACE FUNCTION example_function (input_1 int, input_2 varchar)
RETURNS void AS
$$
DECLARE -- Declaramos las variables que utilizaremos más adelante
    insert_statement TEXT
    res TEXT;
    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
BEGIN
    PERFORM dblink_connect('db2', 'dbname=XX user=XX password=XX'); -- Establecemos la conexión a la otra bdd
    insert_statement = 'INSERT INTO Table VALUES ('||input_1||','''||input_2||''', ''hello world'');';
    res := dblink_exec('db2', insert_statement, true); -- Aquí ejecutamos la línea anterior en la nueva base de datos
    RAISE INFO '%', res; -- En caso de error esto nos muestra la información del problema 
    perform dblink_disconnect('db2'); -- Cerramos la conexión
    
  exception when others then -- Esta línea captura el error
      SELECT dblink_disconnect('db2'); -- Cerramos la conexión
      -- A continuación obtenemos el diagnóstico más detallado del posible error y mostramos en consola la información más relevante del mismo --
      get stacked diagnostics
          v_state   = returned_sqlstate,
          v_msg     = message_text,
          v_detail  = pg_exception_detail,
          v_hint    = pg_exception_hint,
          v_context = pg_exception_context;

      raise notice E'Got exception:
          state  : %
          message: %
          detail : %
          hint   : %
          context: %', v_state, v_msg, v_detail, v_hint, v_context;

      raise notice E'Got exception:
          SQLSTATE: % 
          SQLERRM: %', SQLSTATE, SQLERRM;     

      raise notice '%', message_text;
END 
$$ language plpgsql

Es importante destacar que el código presentado no maneja errores de la mejor manera posible pues captura los errores de forma indiferenciada, lo ideal sería capturar cada uno de los errores de forma diferenciada, pero para esto se requiere saber cuales son los errores que tienen mayor probabilidad de ocurrencia.(de todas formas esto último no es algo fundamental en lo que respecta a la entrega 3 del curso y por lo mismo no es obligación hacerlo)

Para más información sobre el código de error mencionado recomendamos visitar la documentación de PostgreSQL o este link, de donde se extrajo el código para manejar excepciones genéricas en procedimientos almacenados.