Postgresql: prepared statements in loop with date as parameter

Question

I have three prepared statements which create tables from different tables (multiple select statements + join + subqueries) to given date and then join two results of prepared statements namely:

CREATE TABLE _table1 AS
EXECUTE statement1('2000-01-01 00:00:00');

CREATE TABLE _table2 AS
EXECUTE statement2('2000-01-01 00:00:00');

CREATE TABLE _table3 AS
EXECUTE statement3('2000-01-01 00:00:00');

CREATE TABLE _table_all AS
SELECT *
FROM _table1
LEFT JOIN
_table3
ON _table1.var1 = _table3.var1;

Statement3 is build on statement2 = select from _table2 (separate to make it more readable).

What I want to do is:

1. execute the same code for few dates -> pass date as parameter
2. save final table: _table_all as _table_2000-01-01, _table_2000-02-01 itd.

I do not know how to make such a loop / function which creates table and pass table_name and date as parameter.

Expected result:

FOR my_date IN ('2000-01-01 00:00:00', '2001-01-01 00:00:00', '2002-01-01 00:00:00'):

CREATE TABLE _table1 AS
EXECUTE statement1(@my_date);

CREATE TABLE _table2 AS
EXECUTE statement2(@my_date);

CREATE TABLE _table3 AS
EXECUTE statement3(@my_date);

CREATE TABLE _table_@my_date AS
SELECT *
FROM _table1
LEFT JOIN
_table3
ON _table1.var1 = _table3.var1;

DROP TABLE _table1;
DROP TABLE _table2;
DROP TABLE _table3;

Any help?

Answer 1

There are two different forms of EXECUTE in postgres

• The EXECUTE of the procedural PL/PgSQL language, described here:

https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

• The EXECUTE of a prepared statement, as an independant SQL statement (not tied to a procedural language), described here:

https://www.postgresql.org/docs/current/static/sql-execute.html

It's the first one that fits your purpose (dynamic SQL), not the second one. I suspect you were led to prepared statements by finding about EXECUTE and following the wrong trail. Prepared statements are not useful in your context.

See dynamic sql query in postgres for a related Q/A, and in general you'll find examples of dynamic SQL on stackoverflow or elsewhere on the web. For instance, partitioning code tends to look like what you're after.