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.

I think you should use a procedure there is very easy way to pass value. — Shubham Batra
– Shubham Batra, Commented Dec 29, 2015 at 18:13

Clodoaldo Neto · Accepted Answer · 2015-12-29 18:24:24Z

3

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')
);

answered Dec 29, 2015 at 18:24

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

liltitus27 Over a year ago

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 Over a year ago

@liltitus27 An execute will be necessary regardless of if function or anonymous block is being used

Collectives™ on Stack Overflow

How to set and use a variable in a PostgreSQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related