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?

• geozelot: PostgreSQL doesn't support dynamic SQL in its dialect directly (though I remembered that I had read this sweet little hack a long time ago), you would need to use an EXECUTE wrapped in a function if you want to stick to the DB server to make this work.
• geozelot: Also, do these tables have the same structure? If so, consider a merge into a single table as that would be the idiomatic, simpler and more performant way.

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

• Ture Pålsson: If using psycopg2, for extra credit, check out the psycopg2.sql module, which contains means to do this sort of thing without nasty surprises when table names have funny characters in them: psycopg.org/docs/sql.html . (To be honest, I have never used it myself...)
• Caio Mattos: I don't know how psycopg2 work's but I'll try to learn more about.
• Inactivated Account: @CaioMattos are you trying to actually interact with the database using python? Or something else...?
• Caio Mattos: @DPSSpatial I'm looking for some solution with PostgreSQL, unfortunately Python is not my expertise.
• Inactivated Account: @CaioMattos give me some more details on the solution you're after? Perhaps update your question a little bit? we do a lot with python and postgresql... very powerful. psycopg2 is fantastic. If you give me an example of your workflow I can update the code I provided.