current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
2

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 not returning any rows:

SELECT * From internalid_formaltable_name_lookup()

Any idea where I went wrong?

share|improve this question
    
Can't you create a parent table and inherit children so you can query just one table if those have a similar structure? –  mlt Jul 7 '14 at 20:01
    
Also your expression in "RETURN NEXT expr" is empty. –  mlt Jul 7 '14 at 20:05
    
internal_id is a string? I suspect it's a numeric column? –  Erwin Brandstetter Jul 7 '14 at 23:17

1 Answer 1

up vote 3 down vote accepted 
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   ' || formal_table || ' t USING (natural_id_numeric)
       WHERE  i.internal_id = 7166571';   -- assuming internal_id is numeric
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Major points:

  • 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:
How to check if a table exists in a given schema

Then you can work with the pg_class.oid::regclass to escape 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.

share|improve this answer
    
@user3813773: Note the clarification concerning UNION. –  Erwin Brandstetter Jul 8 '14 at 13:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.