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

Question

DDL :

CREATE TABLE - Creates a table with the column names that the user provides.

DROP TABLE - Deletes all rows and removes the table definition from the database.

ALTER TABLE - Adds or removes a column from the table.

I need some examples if it is possible to use 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 the DDL CREATE command in this type? using prepared statements?

Answer 1

Yes, you can use EXECUTE and wrap it in FUNCTION. A function call allows you to pass parameters, and inside FUNCTION, you use string manipulation to modify the DDL statement. Finally, using EXECUTE in FUNCTION does this. 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 "format" of the string function to control the operator and include the parameter that was passed to the function. Of course, your SQL looks rather unusual, especially if you enable CREATE FUNCTION before you call it. This example comes from a data transfer job that I recently did. After CREATING a 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 external SELECT does not select anything, it just makes room for a function call. The number that is passed as a parameter comes from the internal SELECT, which is enclosed in parentheses. A simpler call would be

 select sf.start_mc(42); 

You can wrap everything in a CREATEd FUNCTION. But this means that you are stuck in PostgreSQL and that you need to integrate your database schema and schema changes into your development process as a first-class citizen.

Answer 2

Have you tried

It is not supported by the server, therefore, even if it works in the client-side JDBC driver, I do not 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 is no advantage to this, since you cannot parameterize them, so you cannot write:

 CREATE TABLE ? ( ? text, ...) 

and then specify the placeholder values ​​as query parameters for Statement .

In PostgreSQL, only scheduled statements can be prepared and parameterized on the server side. Currently, this means INSERT , UPDATE , DELETE and SELECT .

You will need to do your own string interpolation and safe quoting according to PostgreSQL rules of the lexical structure - which pretty much meets the requirements of SQL speculation. Wrap all identifiers in "double quotes" and double any literal double quotes, for example "these are literal ""double quotes""" for the table name these are literal "double quotes" .

The fact that you want to do this suggests that you probably have design problems in your circuit, and you may have to think about what you are going to do. Perhaps write a more detailed question on dba.stackexchange.com that explains what you want to achieve with this and why?