Can we use DDL Commands in a prepared statement (PostgreSQL)?

Question

DDL commands follow:

CREATE TABLE — creates a table with the column names the user provides.

DROP TABLE — deletes all rows and removes the table definition from the database.

ALTER TABLE — adds or removes a column from a table.

I need few examples if there is a possibility of using these Commands in PostgreSQL and Java?

public boolean create(Employee employee) {

    try {

        callableStatement = openConnection().prepareCall("{call insert_employee(?,?,?)}");
        callableStatement.setInt(1, employee.getEid());
        callableStatement.setString(2, employee.getEname());
        callableStatement.setInt(3, employee.getSid());     

        i = callableStatement.execute();

        callableStatement.close();

        closeConnection();



    } catch (SQLException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return i;

}

is there any chance of using DDL CREATE command in such type? using prepared statements?

Answer 1

Did you try it?

It isn't supported by the server, so even if it seems to work in the client side JDBC driver I don't recommend it:

regress=> PREPARE CREATE TABLE test ( id serial primary key );
ERROR:  syntax error at or near "CREATE"
LINE 1: PREPARE CREATE TABLE test ( id serial primary key );
                ^

There's no advantage to doing so anyway since you cannot parameterize them, so you can't write:

CREATE TABLE ? ( ? text, ...)

and then specify the placeholder values as query parameters to the Statement.

In PostgreSQL only planned statements may be prepared and parameterised server-side. Currently that means INSERT, UPDATE, DELETE and SELECT.

You'll need to do your own string interpolation and safe quoting according to PostgreSQL's lexical structure rules - which are pretty much those of the SQL spec. Wrap all identifiers in "double quotes" and double any literal double quotes, eg "these are literal ""double quotes""" for the table name these are literal "double quotes".

The very fact that you want to do this suggests that you probably have design issues in your schema and might need to re-think how you're going about things. Maybe post a more detailed question on dba.stackexchange.com that explains what you want to achieve with this and why?