Fonction pour parcourir et sélectionner les données à partir de plusieurs tables

Je suis nouveau sur Postgres et avoir une base de données avec plusieurs tables de la même structure. J'ai besoin de sélectionner les données de chaque table qui correspond à certains critères.

Je pourrais le faire avec un tas de UNION requêtes, mais le nombre de tables j'ai besoin de recherche peut changer au fil du temps, donc je ne veux pas coder en dur comme ça. J'ai essayé de développer une fonction qui boucle par le biais de tables spécifiques (ils ont en commun une convention de nommage) et retourner un tableau d'enregistrements, mais je n'obtiens pas de résultats quand j'ai une requête à la fonction. La fonction de code est ci-dessous:

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;

Je n'obtiens pas les erreurs lorsque j'essaye d'utiliser la fonction, mais ce n'est pas de retourner toutes les lignes:

SELECT * From internalid_formaltable_name_lookup();

Aucune idée de l'endroit où je suis allé mal?

Ne pouvez-vous pas créer une table parent et à l'héritage des enfants de sorte que vous pouvez interroger un tableau si ceux-ci ont une structure similaire?
Aussi votre expression de "RETOUR PROCHAIN expr" est vide.

OriginalL'auteur user3813773 | 2014-07-07

  1. 14
    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;

    Les points principaux:

    • Vous devez utiliser RETURN QUERY EXECUTE à revenir chaque ensemble de lignes.

      EXECUTE, suivie par RETURN NEXT, ne pas faire ce que vous semblez attendre à tous les.

    • Vous avez besoin pour désinfecter les identificateurs. Je suis à l'aide de quote_ident() ici. Ou que votre requête rompre avec les identifiants standard et permettre l'injection SQL!

    • Converti votre col IN (sub-select) plus efficace JOIN.

    • C'est subtilement différent de l'utilisation de a bunch of UNION queries. Il ne pas supprimer les doublons de lignes, et fonctionne en fait comme UNION.

    Personnellement, je préfère construire ce sur le catalogue du système pg_class. Détails:

    Alors vous pouvez travailler avec le pg_class.oid::regclass à s'échapper et schéma-qualifier les noms de table automatiquement. Détails:

    Mais qui dépend des détails de vos besoins et de ... goût.

    n'est-ce aussi travailler sur Redshift?

    OriginalL'auteur Erwin Brandstetter