Function to loop through and select data from multiple tables

Question

I'm new to Postgres and have a database with multiple tables of the same structure. I need to select data from each table that matches certain criteria.

I could do this with a bunch of UNION queries, but the number of tables I need to search can change over time, so I don't want to hard code it like that. I've been trying to develop a function that will loop through specific tables (they have a common naming convention) and return a table of records, but I'm not getting any results when I query the function. Function code is below:

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;

I am not getting any errors when I try to use the function, but it's not returning any rows:

SELECT * From internalid_formaltable_name_lookup();

Any idea where I went wrong?

Answer 1

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.