asked on

There is some function to control the changes made in the database?

Hello,

I have 2 database in postgres, both DB are related using dblink and I need to detect that when making a change in a structure or view could affects the related DB or its views.

The changes I make and need to control are:
1- When I make a change on the structure in DB 1, for example we add a field, and forget to change it in the view or related query. In this case I need a list of views that have related to be able to update them.

2- When I make a change in views in DB 1, and I do not edit all the related queries. In this case, I need a list of queries where the view is used in order to update it.

3- When I make a change in view in DB 1 used by a dblink view in DB 2 without updated it. In this case I need to be notified that the view or structure used in dblink to be able to update it.

Is there any functionality that I can use to be able to do this type of controls?

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilson Net

ASKER

i made a function to search for functions and view using a field name based on pcelba suggest

[code]CREATE OR REPLACE FUNCTION "public"."buscarFunciones"("columna" text)
RETURNS SETOF "public"."informacion_relaciones" AS $BODY$
-- declaro los datos de la vista y las variables
DECLARE datosRelaciones RECORD;
BEGIN
-- @ARTEF Ver Relaciones de Base de Datos - muestras las relaciones a un cmapo o tabla

      -- @MP FOR para recorrer datos - recorro y busco los campos de las tablas
      FOR datosRelaciones IN SELECT n.nspname, cols.table_name, CASE WHEN table_name NOT ILIKE '%pg_%' AND table_name NOT ILIKE '%sql_%' THEN rel_description(n.nspname || '.' || table_name) ELSE '' END AS comentario
            FROM information_schema.columns cols
            INNER JOIN pg_catalog.pg_class c on c.relname=cols.table_name
            INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE (column_name ILIKE ('%' || "columna" || '%') OR (CASE WHEN table_name NOT ILIKE '%pg_%' AND table_name NOT ILIKE '%sql_%' THEN rel_description(n.nspname || '.' || table_name) ELSE '' END) ILIKE ('%' || "columna" || '%')) AND is_updatable = 'YES' GROUP BY n.nspname ,table_name
      LOOP
            -- retornamos los datos de referencia
            RETURN QUERY SELECT 'tabla'::TEXT, datosRelaciones.table_name::TEXT, datosRelaciones.table_name::TEXT, datosRelaciones.comentario::TEXT;

      END LOOP;

      -- @MP FOR para recorrer datos - recorro y proceso los datos de las relaciones de tablas
      FOR datosRelaciones IN SELECT
tc.constraint_name,
tc.constraint_schema || '.' || tc.table_name || '.' || kcu.column_name as physical_full_name,
tc.constraint_schema,
tc.table_name,
kcu.column_name,
ccu.table_name as foreign_table_name,
ccu.column_name as foreign_column_name,
tc.constraint_type
FROM
information_schema.table_constraints as tc
JOIN information_schema.key_column_usage as kcu on (tc.constraint_name = kcu.constraint_name and tc.table_name = kcu.table_name)
JOIN information_schema.constraint_column_usage as ccu on ccu.constraint_name = tc.constraint_name
            WHERE kcu.column_name = "columna"
      LOOP
            -- retornamos los datos de referencia
            RETURN QUERY SELECT 'restricción'::TEXT, datosRelaciones.constraint_name::TEXT, datosRelaciones.physical_full_name::TEXT, ''::TEXT;

      END LOOP;

      -- @MP FOR para recorrer datos - recorro y proceso los datos de las relaciones de vista
      FOR datosRelaciones IN SELECT table_name, view_definition FROM INFORMATION_SCHEMA.views WHERE view_definition ~* "columna"
      LOOP
            -- retornamos los datos de referencia
            RETURN QUERY SELECT 'vista'::TEXT, datosRelaciones.table_name::TEXT, datosRelaciones.view_definition::TEXT, ''::TEXT;

      END LOOP;

      -- @MP FOR para recorrer datos - recorro y proceso los datos de las relaciones de funciones
      FOR datosRelaciones IN SELECT proname AS functionname, prosrc AS source FROM pg_proc WHERE prosrc ~* "columna"
      LOOP
            -- retornamos los datos de referencia
            RETURN QUERY SELECT 'función'::TEXT, datosRelaciones.functionname::TEXT, datosRelaciones.source::TEXT, ''::TEXT;

      END LOOP;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;[/code]

Pavel Celba

You are welcome!