Clever Geek Handbook

Iteration on tables

I need to perform the same operation on multiple tables. Right now the request looks like this:

create view foobar
as 
select this, that 
from here 
where this=1 
union all 
select this,that 
from there 
where this=1
..... .....

etc. for multiple tables. All results are combined.

Is there a way, instead of writing this very long query, where it's easy to get something wrong, write something like

for table in here, there, upthere
 do
 select this, that from $table where this=1

and then combine them all.

I have a query running right now and it will take a while, but this is just curiosity and I didn't know how to google it!

+3


source to share


2 answers


Create VIEW

from UNION

all required tables and then SELECT

from this VIEW

.

Alternatively, you can create VIEW

like this:

CREATE OR REPLACE VIEW table_set AS
SELECT 'table1' as table_name, field1, field2 ...
FROM table1
UNION ALL
SELECT 'table2' as table_name, field1, field2 ...
FROM table2
UNION ALL
SELECT 'table3' as table_name, field1, field2 ...
FROM table3
UNION ALL
...


and SELECT

from this VIEW

like:

SELECT field1, field3
FROM table_set
WHERE table_name IN ('table2','table4')
  AND field5 = 'abc'
+4


source


A view like @Igor desrcibes is one option.

If performance is significant and you don't have a lot of writes, you can use this view to (re) create a <strong> materialized view .

Or for shorter syntax and dynamic selection of source tables, you can use the plpgsql function like this one:

CREATE OR REPLACE FUNCTION f_select_from_tbls(tables text[], cond int)
  RETURNS TABLE (this int, that text) AS
$BODY$
DECLARE
   tbl text; -- automatically avoids SQLi
BEGIN
   FOREACH tbl IN ARRAY tables LOOP
      RETURN QUERY EXECUTE format('
      SELECT this, that
      FROM   %I
      WHERE  this = $1', tbl)
      USING cond;
   END LOOP;
END
$BODY$
  LANGUAGE plpgsql;

Call:

SELECT * FROM f_select_from_tbls('{t1,t2,nonStandard tablename}', 4);

I am assuming that multiple tables share columns with the same name and type (this int, that text)

(you are not defining data types in your question.) The function takes array of text

as table names and value integer

as a condition.

Read more about Looping Through Arrays in the manual.
Learn more about Returning from a function in the manual.



With dynamic SQL, you have to be wary of SQL injection . This threat is neutralized here in two ways:

  • Table names in an array tables

    are entered by a function format()

    that quotes any non-standard table name correctly
  • The parameter value is cond

    passed as through USING

    , which makes SQLi impossible.

Option with VARIADIC

parameter

The goal is to make the function call easier. You can pass a list of text variables instead of building an array from them (the array is inline from parameters). It's all.

CREATE OR REPLACE FUNCTION f_select_from_tbls2(cond int, VARIADIC tables text[])
  RETURNS TABLE (this int, that text) AS
$BODY$
DECLARE
   tbl text; -- automatically avoids SQLi
BEGIN
   FOREACH tbl IN ARRAY tables LOOP
      RETURN QUERY EXECUTE format('
      SELECT this, that
      FROM   %I
      WHERE  this = $1', tbl)
      USING cond;
   END LOOP;
END
$BODY$
  LANGUAGE plpgsql;

I put it tables

last in this case to have an open end for the parameter VARIADIC

. Call:

SELECT * FROM f_select_from_tbls2(4, 't1','t2','iLLegal name')
+1


source






More articles:


All Articles