• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Blog
  • Careers

  • CasePointer

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    Views across many similar tables

    Jeff Boes

    By Jeff Boes
    July 13, 2010

    An application I’m working on has a host of (a dozen or so) status tables, each containing various rows that reflect the state of associated rows in other tables. For instance:

    Table "public.inventory"
    ...
    status_code      | character varying(50)       | not null
    
    Table "public.inventory_statuses"
    code          | character varying(50)       | not null
    display_label | character varying(70)       | not null
    
    SELECT * FROM inventory_statuses;
    
      code    | display_label
    -----------+---------------
    ordered   | Ordered
    shipped   | Shipped
    returned  | Returned
    repaired  | Repaired
    

    etc.

    Several of the codes are common to several tables. For instance, “void” is a status that occurs in seven tables. The application cares about this; there are code-level triggers that will respond to a change of status to “void” in one table, and pass that information along to another table higher up the chain.

    Since I wasn’t present at the birth of the system (nor do I have unlimited memory to keep 180+ codes in my head), I needed a way to answer the question, “In which table(s) does status ‘foo’ occur?” This was made rather easier by attention to detail early on: each of the status tables was named “*_statuses”; each primary key was named “code”; and each human-readable description field was named “display_label”. I wrote a Pl/PgSQL function to create a view spanning all the tables. (I could have just created the SQL by hand, but I wanted a way to reproduce this effort later, if tables are added, dropped, or modified.)

    CREATE FUNCTION create_all_statuses()
    RETURNS VOID
    LANGUAGE 'plpgsql'
    AS $$
    DECLARE
       stmt TEXT;
       tbl RECORD;
    BEGIN
       stmt := '';
       FOR tbl IN EXECUTE $SQL$
    SELECT DISTINCT table_name
    FROM information_schema.columns a
    JOIN information_schema.columns b
    USING (table_name)
    JOIN information_schema.tables t
    USING (table_name)
    WHERE a.column_name = 'code'
    AND   b.column_name = 'display_label'
    AND   table_name ~ '_statuses$'
    AND   t.table_type  = 'BASE TABLE'
    $SQL$
       LOOP
           IF (LENGTH(stmt) > 0)
           THEN
               stmt := stmt || ' UNION ';
           END IF;
           stmt := stmt || 'SELECT code, display_label, ' ||
               quote_literal(tbl.table_name) ||
               ' AS table_name FROM ' ||
               quote_ident(tbl.table_name);
       END LOOP;
    
       EXECUTE 'CREATE VIEW all_statuses AS ' || stmt;
       RETURN;
    END;
    $$;
    

    Now it’s easy to answer the question:

    select * from all_statuses where code = 'void';
    
    code | display_label |              table_name
    ------+---------------+--------------------------------------
    void | Void          | inventory_statuses
    void | Void          | parcel_statuses
    void | Void          | pick_list_statuses
    

    etc.

    If your database uses boilerplate columns such as “last_modified” or “date_created” to record timestamps on rows, you could use similar logic to create a view that would tell you which tables were the most recently modified.

    database postgres


    Comments