Can we use DDL Commands in a prepared statement (PostgreSQL)?
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?
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.