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

¿Es posible buscar en cada columna de cada tabla un valor particular en PostgreSQL?

Una pregunta similar está disponible aquí para Oracle.

¿Qué hay de tirar el contenido de la base de datos, y luego usar 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. Simplemente cambie --inserts a --column-inserts . De esta forma, también puede buscar nombres de columna específicos. Pero si buscara nombres de columna, probablemente dejarí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í hay una función pl / pgsql que ubica registros donde cualquier columna contiene un valor específico. Toma como argumentos el valor de buscar en formato de texto, una matriz de nombres de tabla para buscar (por defecto a todas las tablas) y una matriz de nombres de esquema (por defecto todos los nombres de esquema).

Devuelve una estructura de tabla con el esquema, el nombre de la tabla, el nombre de la columna y la 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) 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 EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L', schemaname, tablename, columnname, needle ) INTO rowctid; IF rowctid is not null THEN RETURN NEXT; END IF; END LOOP; END; $$ language plpgsql; 

EDITAR : este código es para PG 9.1 o más reciente. Además, es posible que desee que la versión en github se base en el mismo principio pero agregue algo de velocidad y mejoras en los informes.

Ejemplos de uso en una base de datos de prueba:

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

 seleccione * de search_columns ('foobar');
  schemaname |  nombre de tabla |  nombre de columna |  rowctid 
 ------------ + ----------- + ------------ + ---------
  público |  s3 |  usename |  (0,11)
  público |  s2 |  relname |  (7,29)
  público |  w |  cuerpo |  (0,2)
 (3 filas)

Busque en una tabla específica:

  selecciona * de search_columns ('foobar', '{w}');
  schemaname |  nombre de tabla |  nombre de columna |  rowctid 
 ------------ + ----------- + ------------ + ---------
  público |  w |  cuerpo |  (0,2)
 (1 fila)

Buscar en un subconjunto de tablas obtenidas de un seleccionar:

 select * from search_columns ('foobar', array (seleccione table_name :: name de information_schema.tables donde table_name como 's%'), array ['public']);
  schemaname |  nombre de tabla |  nombre de columna |  rowctid 
 ------------ + ----------- + ------------ + ---------
  público |  s2 |  relname |  (7,29)
  público |  s3 |  usename |  (0,11)
 (2 filas)

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

 select * from public.w donde ctid = '(0,2)';
  título |  cuerpo |  tsv         
 ------- + -------- + ---------------------
  toto |  foobar |  'foobar': 2 'toto': 1

Para probar nuevamente una expresión regular en lugar de una igualdad estricta, como grep, esto:

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

puede ser cambiado a:

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

La única herramienta que conozco que puede hacer eso es: SQL Workbench / J: http://www.sql-workbench.net/

Una herramienta basada en Java / JDBC que ofrece un “comando” SQL especial (propietario) para buscar a través de todas las tablas (o solo seleccionadas) en una base de datos:

http://www.sql-workbench.eu/manual/wb-commands.html#command-search-data
http://www.sql-workbench.eu/wbgrepdata_png.html

Y si alguien piensa que podría ayudar. Aquí está la función de @Daniel Vérité, con otro parámetro que acepta nombres de columnas que se pueden usar en la búsqueda. De esta forma, disminuye el tiempo de procesamiento. Al menos en mi prueba redujo mucho.

 CREATE OR REPLACE FUNCTION search_columns( needle text, haystack_columns name[] default '{}', haystack_tables name[] default '{}', haystack_schema name[] default '{public}' ) 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) WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}') AND c.table_schema=ANY(haystack_schema) AND (c.column_name=ANY(haystack_columns) OR haystack_columns='{}') AND t.table_type='BASE TABLE' LOOP EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L', schemaname, tablename, columnname, needle ) INTO rowctid; IF rowctid is not null THEN RETURN NEXT; END IF; END LOOP; END; $$ language plpgsql; 

A continuación, se muestra un ejemplo del uso de la función de búsqueda creada anteriormente.

 SELECT * FROM search_columns('86192700' , array(SELECT DISTINCT a.column_name::name FROM information_schema.columns AS a INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name) WHERE a.column_name iLIKE '%cep%' AND b.table_type = 'BASE TABLE' AND b.table_schema = 'public' ) , array(SELECT b.table_name::name FROM information_schema.columns AS a INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name) WHERE a.column_name iLIKE '%cep%' AND b.table_type = 'BASE TABLE' AND b.table_schema = 'public') ); 

Sin almacenar un nuevo procedimiento, puede usar un bloque de código y ejecutar para obtener una tabla de ocurrencias. Puede filtrar resultados por esquema, tabla o nombre de columna.

 DO $$ DECLARE value int := 0; sql text := 'The constructed select statement'; rec1 record; rec2 record; BEGIN DROP TABLE IF EXISTS _x; CREATE TEMPORARY TABLE _x ( schema_name text, table_name text, column_name text, found text ); FOR rec1 IN SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE table_name <> '_x' AND UPPER(column_name) LIKE UPPER('%%') AND table_schema <> 'pg_catalog' AND table_schema <> 'information_schema' AND data_type IN ('character varying', 'text', 'character', 'char', 'varchar') LOOP sql := concat('SELECT ', rec1."column_name", ' AS "found" FROM ',rec1."table_schema" , '.',rec1."table_name" , ' WHERE UPPER(',rec1."column_name" , ') LIKE UPPER(''','%my_substring_to_find_goes_here%' , ''')'); RAISE NOTICE '%', sql; BEGIN FOR rec2 IN EXECUTE sql LOOP RAISE NOTICE '%', sql; INSERT INTO _x VALUES (rec1."table_schema", rec1."table_name", rec1."column_name", rec2."found"); END LOOP; EXCEPTION WHEN OTHERS THEN END; END LOOP; END; $$; SELECT * FROM _x; 

Aquí está la función de @Daniel Vérité con la funcionalidad de informes de progreso. Informa el progreso de tres maneras:

  1. por AVISO DE RAISE;
  2. al disminuir el valor de la secuencia {progress_seq} suministrada desde {número total de columnas para buscar en} hasta 0;
  3. escribiendo el progreso junto con las tablas encontradas en el archivo de texto, ubicado en c: \ windows \ temp \ {progress_seq} .txt.

_

 CREATE OR REPLACE FUNCTION search_columns( needle text, haystack_tables name[] default '{}', haystack_schema name[] default '{public}', progress_seq text default NULL ) RETURNS table(schemaname text, tablename text, columnname text, rowctid text) AS $$ DECLARE currenttable text; columnscount integer; foundintables text[]; foundincolumns text[]; begin currenttable=''; columnscount = (SELECT count(1) FROM information_schema.columns c JOIN information_schema.tables t ON (t.table_name=c.table_name AND t.table_schema=c.table_schema) WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}') AND c.table_schema=ANY(haystack_schema) AND t.table_type='BASE TABLE')::integer; PERFORM setval(progress_seq::regclass, columnscount); 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) WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}') AND c.table_schema=ANY(haystack_schema) AND t.table_type='BASE TABLE' LOOP EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L', schemaname, tablename, columnname, needle ) INTO rowctid; IF rowctid is not null THEN RETURN NEXT; foundintables = foundintables || tablename; foundincolumns = foundincolumns || columnname; RAISE NOTICE 'FOUND! %, %, %, %', schemaname,tablename,columnname, rowctid; END IF; IF (progress_seq IS NOT NULL) THEN PERFORM nextval(progress_seq::regclass); END IF; IF(currenttable<>tablename) THEN currenttable=tablename; IF (progress_seq IS NOT NULL) THEN RAISE NOTICE 'Columns left to look in: %; looking in table: %', currval(progress_seq::regclass), tablename; EXECUTE 'COPY (SELECT unnest(string_to_array(''Current table (column ' || columnscount-currval(progress_seq::regclass) || ' of ' || columnscount || '): ' || tablename || '\n\nFound in tables/columns:\n' || COALESCE( (SELECT string_agg(c1 || '/' || c2, '\n') FROM (SELECT unnest(foundintables) AS c1,unnest(foundincolumns) AS c2) AS t1) , '') || ''',''\n''))) TO ''c:\WINDOWS\temp\' || progress_seq || '.txt'''; END IF; END IF; END LOOP; END; $$ language plpgsql; 

buscar en cada columna de cada tabla un valor particular

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

Asumiendo:

  • Encuentre cualquier fila con cualquier columna que contenga el valor dado en su representación de texto, en lugar de igualar el valor dado.
  • Devuelve el nombre de la tabla ( regclass ) y el puntero del elemento ( ctid ), porque es más simple.

Aquí está una manera muerta simple, 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 %st WHERE t::text ~~ %L' , _tbl, '%' || _like_pattern || '%') USING _tbl; END LOOP; END $func$ LANGUAGE plpgsql; 

Llamada:

 SELECT * FROM search_whole_db('mypattern'); 

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

¿Por qué un poco sucio?

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

  • separador de columna: por defecto
  • toda la fila está encerrada entre paréntesis: ()
  • algunos valores están entre comillas dobles "
  • \ se puede agregar como escape char

Y la representación de 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.

Cada fila calificada se devuelve una sola vez , incluso cuando coincide muchas veces (a diferencia de otras respuestas aquí).

Esto busca en toda la base de datos excepto en los catálogos del sistema. Por lo general, tomará un largo 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 tipo de identificador de objeto de clase de regclass se representa como nombre de tabla, calificado por esquema cuando es necesario para desambiguar de acuerdo con la search_path actual:

  • Encuentre el nombre de tabla referenciado usando tabla, campo y nombre de esquema

¿Qué es el ctid ?

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

Es posible que desee escapar de los personajes con un significado especial en el patrón de búsqueda. Ver:

  • PostgreSQL: función de escape de expresión regular