Function to loop through and select data from multiple tables

by 
CREATE OR REPLACE FUNCTION public.internalid_formaltable_name_lookup()
  RETURNS TABLE(natural_id text, name text, natural_id_numeric text)
  LANGUAGE plpgsql 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$;

You have to use RETURN QUERY EXECUTE to return each set of rows.
EXECUTE, followed by RETURN NEXT, does not do what you seem to expect at all.

You need to sanitize identifiers. I am using quote_ident() here. Or your query will break with non-standard identifiers and allow SQL injection!

Converted your col IN (sub-select) to a more efficient JOIN.

This is subtly different from using a bunch of UNION queries. It does not remove duplicate rows, and actually works like UNION ALL.

Personally, I’d rather build this on the system catalog pg_class or the system view pg_tables. Details:

  • How to check if a table exists in a given schema

Then you can work with the pg_class.oid::regclass to escape and schema-qualify table names automatically. Details:

  • Table name as a PostgreSQL function parameter
  • Search across multiple tables and also display table name in resulting rows

But that depends on the details of your requirements and … taste.

Leave a Comment

tech