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

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?

Solution

Yes you can, if you use EXECUTE and wrap it in a FUNCTION. The function call allows you to pass parameters, and inside the FUNCTION you use string manipulation to modify the DDL statement. Finally, the use of EXECUTE in the FUNCTION makes it so. Here is a simple example of a parameterized CREATE SEQUENCE statement...

DROP FUNCTION sf.start_mc(integer);

CREATE FUNCTION sf.start_mc(thefirst integer) RETURNS void AS $$
BEGIN
    EXECUTE format('CREATE SEQUENCE sf.mastercase START %s',thefirst);
END;

$$ LANGUAGE plpgsql;

We use the string function "format" to manipulate the statement and include the parameter that was passed to the function. Of course, your SQL looks rather unusual, particularly if you include the CREATE FUNCTION before you call it. This example comes from a data migration job that I recently did. After CREATEing the function we used it like this:

DROP SEQUENCE sf.mastercase;

-- the following uses the above function to set the starting value of a new sequence based on the last used
-- in the widget table
select sf.start_mc((select substring("widgetId",4)::integer + 1 from widgets
where "widgetId" like 'MC-%'
order by "widgetId" desc
limit 1));

Note that the outer SELECT doesn't select anything, it just makes a place to do the function call. The number that is passed as a parameter comes from the inner SELECT which is wrapped in parentheses. A simpler call would be

select sf.start_mc(42);

You can wrap anything in a CREATEd FUNCTION. But this does mean that you are stuck with PostgreSQL and that you need to integrate your DB schema, and schema changes, into your development process as a first class citizen.