Soy nuevo en Postgres y tiene una base de datos con varias tablas de la misma estructura. Tengo que seleccionar los datos de cada tabla que coincide con ciertos criterios.

Yo podría hacer esto con un montón de UNION consultas, pero el número de tablas que necesito para la búsqueda se puede cambiar con el tiempo, así que no quiero escribir de esa manera. He estado tratando de desarrollar una función que se realizará un bucle a través de tablas específicas (tienen un común convención de nomenclatura) y el retorno de una tabla de registros, pero no voy a conseguir ningún resultado, cuando me consulta de la función. El código de la función es el siguiente:

CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
  RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
$BODY$
DECLARE
    formal_table text;
begin
  FOR formal_table IN
    select table_name from information_schema.tables
    where table_schema = 'public' and table_name like 'formaltable%'
  LOOP
    EXECUTE 'SELECT natural_id, name, natural_id_numeric
             FROM ' || formal_table || 
           ' WHERE natural_id_numeric IN (
                select natural_id_numeric from internal_idlookup
                where internal_id = ''7166571'')';
    RETURN NEXT;
 END LOOP;
 Return;
END;
$BODY$
  LANGUAGE plpgsql;

No estoy recibiendo ningún error cuando trato de usar la función, pero no devuelve ninguna fila:

SELECT * From internalid_formaltable_name_lookup();

Alguna idea de donde me salió mal?

  • No se puede crear una tabla padre y heredar a los niños de modo que usted puede consultar en una sola tabla, si aquellos que tienen una estructura similar?
  • También su expresión en «VOLVER el PRÓXIMO expr» está vacía.
InformationsquelleAutor user3813773 | 2014-07-07

1 Comentario

  1. 17
    CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
      RETURNS TABLE(natural_id text, name text, natural_id_numeric text) AS
    $func$
    DECLARE
       formal_table text;
    BEGIN
       FOR formal_table IN
          SELECT quote_ident(table_name)
          FROM   information_schema.tables
          WHERE  table_schema = 'public'
          AND    table_name LIKE 'formaltable%'
       LOOP
          RETURN QUERY EXECUTE
          'SELECT t.natural_id, t.name, t.natural_id_numeric
           FROM   internal_idlookup i 
           JOIN   public.' || formal_table || ' t USING (natural_id_numeric)
           WHERE  i.internal_id = 7166571';   -- assuming internal_id is numeric
       END LOOP;
    END
    $func$  LANGUAGE plpgsql;

    Puntos principales:

    • Tienes que utilizar VOLVER CONSULTA EJECUTAR a volver cada conjunto de filas.

      EXECUTE, seguido por RETURN NEXT, no hacer lo que parecen esperar que en todos los.

    • Usted necesita para desinfectar los identificadores. Estoy usando quote_ident() aquí. O su consulta romper con los no-identificadores estándar y permiten la inyección de SQL!

    • Convertido su col IN (sub-select) a un más eficiente JOIN.

    • Este es sutilmente diferente del uso de a bunch of UNION queries. No no quitar filas duplicadas, y en realidad funciona como UNIÓN.

    Personalmente, prefiero construir esta en el catálogo del sistema pg_class. Detalles:

    Entonces usted puede trabajar con el pg_class.oid::regclass para escapar del esquema y de calificar los nombres de tabla automáticamente. Detalles:

    Pero que depende de los detalles de sus requisitos y … gusto.

    • ¿esto también funciona en el corrimiento Al rojo?

Dejar respuesta

Please enter your comment!
Please enter your name here