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?

Can't you create a parent table and inherit children so you can query just one table if those have a similar structure? — mlt
– mlt, Commented Jul 7, 2014 at 20:01

Erwin Brandstetter · Accepted Answer · 2021-08-27 01:34:08Z

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:

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

Thank you for pointing out to use quote_ident(). I used quote_literal() since the former add double-quotes (") whereas the latter singe-quotes (').

Collectives™ on Stack Overflow

Function to loop through and select data from multiple tables

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related