Passing SQL null as a parameter value to an SQL statement using PostpreSQL libpqtypes PQputf or PQexecf functions

Question

My friends,

I want to use the libpqtypes PostgreSQL library with an existing database from application C.

One SQL statement that I have to execute is an insert into a table with an int column, which may be null. This one has a circuit that I simplify here to just:

create table t ( a int null ); 

I read the documentation on the libpqtypes PQputf and PQexecf functions, but I see no mention of how to pass SQL null values ​​to parameterized SQL statements!

I do not want to pass C NULL because it is an int column, and I want the value stored in the database to be SQL null, not 0. Therefore, I do not want to do this:

 PQexecf(conn, "INSERT INTO t (a) VALUES (%int4)", NULL); 

Although I could include null directly when creating the format string of the SQL insert string that is passed to PQexecf, I don't want to do this, because sometimes I have to insert a non-zero value. Therefore, I do not want to do this:

 PQexecf(conn, "INSERT INTO t (a) VALUES (null)"); 

It is my desire and desire to use a single line expression of a SQL expression of a general format when calling PQexecf and just pass the value of the null parameter somehow when I have no value. I want to do something like this where SQL_NULL is a way to tell libpqtypes that the SQL number should be inserted into the database:

 void insert(int *a) { /* Other source codes. */ PQexecf(conn, "INSERT INTO t (a) VALUES (%int4)", a ? *a : SQL_NULL); /* Other source codes. */ } 

Is it possible? How should I do it?

Many, many thanks to you!

Answer 1

As far as I can see, this is not possible using only libpqtypes (but then I never used libpqtypes).

A workaround that doesn't require a lot of C code is to select a single value for "NULL" and use NULLIF in the statement to convert it to SQL NULL:

 // treat -1 as NULL PQexecf(conn, "INSERT INTO t (a) VALUES (NULLLIF(%int4, -1))", a ? *a : -1); 

Answer 2

Use the built-in type "%null" . Find this page in the documentation for the "pqt schema".

 // doesn't require a variable argument. PQexecf(conn, "INSERT INTO t (a) VALUES (%null)");