Can we use DDL Commands in a prepared statement (P

问题:

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?

回答1:

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.

回答2:

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?