0

I have a query that I have to update every month and generate a new table. There are several references to this table, and I always seem to miss one. I was wondering if there is a way that I can set a local variable and reuse it through out the query. As an example:

    DECLARE 'table'||to_char(curent_timestamp, 'MON') ||
               to_char(current_timestanp,"YY") AS table_ref;
    CREATE TABLE table_ref AS select * FROM base_table;
    SELECT * FROM table_ref;

Thanks.

windsormatic
  • 173
  • 1
  • 2
  • 8
  • What is the context in which this is used? If you just wanted the contents, you could simply query `base_table` directly, so I assume you are trying to create a copy at a point-in-time and "timestamp" the table name? Is that correct? – Nick Jun 10 '16 at 00:12
  • Totally - the query would be more complex than that. I was just going with a simple base example. – windsormatic Jun 10 '16 at 00:15
  • Question should be closed because it is obsolete (see comments on answer below) and OP formulated a new question (http://stackoverflow.com/q/37752296/3304426). – Patrick Jun 11 '16 at 06:28

1 Answers1

0

You can use FORMAT() and EXECUTE to execute your dynamic SQL, like so:

DO $$

DECLARE table_name TEXT;

BEGIN

   SELECT FORMAT('table%I',TO_CHAR(CURRENT_TIMESTAMP,'MONYY')) INTO table_name; -- ex. tableJUN16

   EXECUTE FORMAT('CREATE TABLE %I AS SELECT * FROM base_table;',table_name);

END; $$ LANGUAGE PLPGSQL;

This will create your new table from the base_table with your dynamic name.

https://www.postgresql.org/docs/current/static/functions-string.html

Nick
  • 7,103
  • 2
  • 21
  • 43
  • I'm not at my computer now, but I want to reference this table several times through out the script. Is that possible? – windsormatic Jun 10 '16 at 01:59
  • @windsormatic - Yea. I'll update it to suit that better. – Nick Jun 10 '16 at 02:22
  • Use %I substitution symbol in function `format` for SQL identifier. It ensure correct escaping and protection against SQL injection. – Pavel Stehule Jun 10 '16 at 06:32
  • That would probably work - but I am in postgreSQL 8.2. – windsormatic Jun 10 '16 at 15:30
  • @windsormatic - That is a very old version. Neither `EXECUTE` nor `FORMAT` are supported in 8.2. I was suggest you add the `postgresql-8.2` tag to your question so that you can get answers specific to your version. – Nick Jun 10 '16 at 15:33
  • @Nicarus `EXECUTE` is available in PG8.2, `format()` isn't. The OP followed your advice on the tag, but made it a new question. – Patrick Jun 10 '16 at 16:32
  • I did make it in a new question as I felt as though the provided answer would provide color for those that might be looking for a solution to this problem utilizing a newer version of PG. – windsormatic Jun 10 '16 at 21:46
  • Then you should delete this question because it is obsolete – Patrick Jun 11 '16 at 06:25