How to set and use a variable in a PostgreSQL Query

Question

I need to dynamically create a table everyday. Currently, this is my working solution:

DO
$$
 BEGIN
 EXECUTE format('
 CREATE TABLE schema.%I (
 id NUMERIC,
 field1 TEXT,
 field2 TEXT
 )
 WITH (
 OIDS=FALSE
 );
 GRANT ALL ON TABLE schema.%I TO role1;
 GRANT ALL ON TABLE schema.%I TO role2;', 
 'table_name_' || to_char(CURRENT_DATE,'YYYYMMDD'),
 'table_name_' || to_char(CURRENT_DATE,'YYYYMMDD'),
 'table_name_' || to_char(CURRENT_DATE,'YYYYMMDD')
 );
 END;
$$ LANGUAGE plpgsql;

However, you can see how I have to add an argument to format command for every mention of the table I'm trying to create. Add some constraints, indexes, etc., and this becomes untenable.

How can I accomplish this by setting the table name variable once and then using that over and over again? Ideally, I would like the solution to be executable from within a PGAdmin query window. That being said, however, this will end up being stored in a sql file and executed from a script.

I've tried the /set thing I've seen all over while searching for a solution, but I always end up with a syntax error starting with the slash.

• Shubham Batra: I think you should use a procedure there is very easy way to pass value.
• liltitus27: @ShubhamBatra that's a good idea, i'll look into that.

Answer 1

Use the position field n$

EXECUTE format('
 CREATE TABLE schema.%1$I (
 id NUMERIC,
 field1 TEXT,
 field2 TEXT
 )
 WITH (
 OIDS=FALSE
 );
 GRANT ALL ON TABLE schema.%1$I TO role1;
 GRANT ALL ON TABLE schema.%1$I TO role2;', 
 'table_name_' || to_char(CURRENT_DATE,'YYYYMMDD')
);

• liltitus27: not exactly what i was expecting, but mitigates the exact problem i was trying to solve; excellent answer, thank you! for what it's worth (for others reading this thread), @ShubhamBatra is correct that i should be using a stored procedure for this type of task.
• Clodoaldo Neto: @liltitus27 An execute will be necessary regardless of if function or anonymous block is being used