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:

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

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

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. Truncating all tables in a Postgres database
  3. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  4. Execute a dynamic crosstab query
  5. Dynamic SQL (EXECUTE) as condition for IF statement
  6. Test for null in function with varying parameters
  7. Return SETOF rows from PostgreSQL function
  8. Error when setting n_distinct using a plpgsql variable
  9. Postgres FOR LOOP
  10. Dynamically generate columns for crosstab in PostgreSQL
  11. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  12. Loop through columns of RECORD
  13. Format specifier for integer variables in format() for EXECUTE?
  14. Table name as a PostgreSQL function parameter
  15. Refactor a PL/pgSQL function to return the output of various SELECT queries
  16. The forgotten assignment operator “=” and the commonplace “:=”
  17. Unnest multiple arrays in parallel
  18. How to set value of composite variable field using dynamic SQL
  19. PostgreSQL parameterized Order By / Limit in table function
  20. How do I do large non-blocking updates in PostgreSQL?
  21. Is there a way to disable function overloading in Postgres
  22. Can I make a plpgsql function return an integer without using a variable?
  23. Returning from a function with OUT parameter
  24. Use variable set by psql meta-command inside of DO block
  25. COPY with dynamic file name
  26. ERROR: unterminated quoted string at or near
  27. How can I send some http request from postgresql function or trigger
  28. Select from a table variable
  29. Hashing a String to a Numeric Value in PostgreSQL
  30. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function

Leave a Comment