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