Execute loop in PostgreSQL query

Question

How to execute for each table in the list one fixed query replacing just %table% in PostgreSQL (like variables and loops)

sql = 'select * from $table$ where...some_filter'

$table = {tb1, tb2, tb3...}

Somenone could clarify?

Answer 1

Though not specifically GIS related, I'm working on something similar, so here's a quick solution.

Assuming you're using psycopg2 and already have a connection and a cursor set up to your database:

#array of table names
tables = ['schools_current', 'schools_projected']

for x in tables:
        sql_statement = 'select * from dpsdata.' + x + ';' 
        #hard-coded schema name above so you'll have to set your own
        print sql_statement
        curSource.execute(sql_statement)
        rows = curSource.fetchall()
        print rows