Function to loop through and select data from multiple tables

dynamic-sql, for-loop, plpgsql, postgresql, set-returning-functions

Solution

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.

Problem

Original source

Related problems

• Table name as a PostgreSQL function parameter
• How to check if a table exists in a given schema