¿Cómo buscar un valor específico en todas las tablas (PostgreSQL)?

Tenemos el resultado a este rompecabezas, al menos eso esperamos. Si sigues con inquietudes dínoslo, que sin dudas

¿Qué tal si volcamos el contenido de la base de datos y luego usamos grep?

$ pg_dump --data-only --inserts -U postgres your-db-name > a.tmp
$ grep United a.tmp
INSERT INTO countries VALUES ('US', 'United States');
INSERT INTO countries VALUES ('GB', 'United Kingdom');

La misma utilidad, pg_dump, puede incluir nombres de columna en la salida. Solo cambia --inserts a --column-inserts. De esa forma, también puede buscar nombres de columnas específicos. Pero si estuviera buscando nombres de columnas, probablemente volcaría el esquema en lugar de los datos.

$ pg_dump --data-only --column-inserts -U postgres your-db-name > a.tmp
$ grep country_code a.tmp
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('US', 'United  States');
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('GB', 'United Kingdom');

Aquí está un función pl / pgsql que ubica registros donde cualquier columna contiene un valor específico. Toma como argumentos el valor a buscar en formato de texto, un array de nombres de tablas para buscar (por defecto en todas las tablas) y un array de nombres de esquema (por defecto todos los nombres de esquema).

Devuelve una estructura de tabla con esquema, nombre de tabla, nombre de columna y pseudocolumna ctid (ubicación física no duradera de la fila en la tabla, consulte Columnas del sistema)

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    haystack_tables name[] default '',
    haystack_schema name[] default ''
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
  FOR schemaname,tablename,columnname IN
      SELECT c.table_schema,c.table_name,c.column_name
      FROM information_schema.columns c
        JOIN information_schema.tables t ON
          (t.table_name=c.table_name AND t.table_schema=c.table_schema)
        JOIN information_schema.table_privileges p ON
          (t.table_name=p.table_name AND t.table_schema=p.table_schema
              AND p.privilege_type='SELECT')
        JOIN information_schema.schemata s ON
          (s.schema_name=t.table_schema)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='')
        AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='')
        AND t.table_type='BASE TABLE'
  LOOP
    FOR rowctid IN
      EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
       schemaname,
       tablename,
       columnname,
       needle
      )
    LOOP
      -- uncomment next line to get some progress report
      -- RAISE NOTICE 'hit in %.%', schemaname, tablename;
      RETURN NEXT;
    END LOOP;
 END LOOP;
END;
$$ language plpgsql;

Ver también el versión en github basado en el mismo principio pero agregando algunas mejoras de velocidad y generación de informes.

Ejemplos de uso en una base de datos de prueba:

• Buscar en todas las tablas dentro del esquema público:

select * from search_columns('foobar');
 schemaname | tablename | columnname | rowctid 
------------+-----------+------------+---------
 public     | s3        | usename    | (0,11)
 public     | s2        | relname    | (7,29)
 public     | w         | body       | (0,2)
(3 rows)

• Buscar en una tabla específica:

 select * from search_columns('foobar','w');
 schemaname | tablename | columnname | rowctid 
------------+-----------+------------+---------
 public     | w         | body       | (0,2)
(1 row)

• Busque en un subconjunto de tablas obtenidas de una selección:

select * from search_columns('foobar', array(select table_name::name from information_schema.tables where table_name like 's%'), array['public']);
 schemaname | tablename | columnname | rowctid 
------------+-----------+------------+---------
 public     | s2        | relname    | (7,29)
 public     | s3        | usename    | (0,11)
(2 rows)

• Obtenga una fila de resultados con la tabla base correspondiente y ctid:

select * from public.w where ctid='(0,2)';
 title |  body  |         tsv         
-------+--------+---------------------
 toto  | foobar | 'foobar':2 'toto':1

Variantes

• Para probar con una expresión regular en lugar de una igualdad estricta, como grep, esta parte de la consulta:

  SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L

  se puede cambiar a:

  SELECT ctid FROM %I.%I WHERE cast(%I as text) ~ %L

• Para comparaciones que no distinguen entre mayúsculas y minúsculas, puede escribir:

  SELECT ctid FROM %I.%I WHERE lower(cast(%I as text)) = lower(%L)

para buscar en cada columna de cada tabla un valor particular

Esto no define cómo coincidir exactamente.
Tampoco define qué devolver exactamente.

Asumiendo:

• Encuentra cualquier fila con cualquier columna conteniendo el valor dado en su representación de texto – en contraposición a igualando el valor dado.
• Devuelve el nombre de la tabla (regclass) y el ID de tupla (ctid), porque eso es lo más simple.

Aquí hay una forma sencilla, rápida y ligeramente sucia:

CREATE OR REPLACE FUNCTION search_whole_db(_like_pattern text)
  RETURNS TABLE(_tbl regclass, _ctid tid) AS
$func$
BEGIN
   FOR _tbl IN
      SELECT c.oid::regclass
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = relnamespace
      WHERE  c.relkind = 'r'                           -- only tables
      AND    n.nspname !~ '^(pg_|information_schema)'  -- exclude system schemas
      ORDER BY n.nspname, c.relname
   LOOP
      RETURN QUERY EXECUTE format(
         'SELECT $1, ctid FROM %s t WHERE t::text ~~ %L'
       , _tbl, '%' || _like_pattern || '%')
      USING _tbl;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Llamar:

SELECT * FROM search_whole_db('mypattern');

Proporcionar el patrón de búsqueda sin incluir %.

¿Por qué un poco sucio?

Si los separadores y decoradores de la fila en text La representación puede ser parte del patrón de búsqueda, puede haber false positivos:

• separador de columnas: , por defecto
• toda la fila está entre paréntesis:()
• algunos valores están entre comillas dobles "
• se puede agregar como carácter de escape

Y la representación del texto de algunas columnas puede depender de la configuración local, pero esa ambigüedad es inherente a la pregunta, no a mi solución.

Se devuelve cada fila calificada una vez solo, incluso cuando coincide varias veces (a diferencia de otras respuestas aquí).

Esto busca en toda la base de datos excepto en los catálogos del sistema. Normalmente tomar mucho tiempo para terminar. Es posible que desee restringir a ciertos esquemas / tablas (o incluso columnas) como se muestra en otras respuestas. O agregue avisos y un indicador de progreso, también demostrado en otra respuesta.

El regclass El tipo de identificador de objeto se representa como el nombre de la tabla, calificado por esquema cuando sea necesario para eliminar la ambigüedad de acuerdo con el search_path:

• Busque el nombre de la tabla a la que se hace referencia utilizando el nombre de la tabla, el campo y el esquema

Cuál es el ctid?

• ¿Cómo descompongo ctid en números de página y fila?

Es posible que desee utilizar caracteres de escape con un significado especial en el patrón de búsqueda. Ver:

• Función de escape para expresiones regulares o patrones LIKE

---

---

Utiliza Nuestro Buscador