"CREATE SCHEMA foo ..." query from C extension in PostgreSQL (using SPI_execute_with_args)

Question

I'm trying to execute a SQL query from a C extension built for PostgreSQL, using the Server Programming Interface (SPI). The query should create a new schema with quite a large number of tables. (Basically it should setup a workspace for users to work in.) But since the user should be able to create multiple workspaces, I don't know the schema name when writing the script. So I need a way to supply this at runtime. But I can't get it to work.

I'm trying to do this by using SPI_execute_with_args as the documentation states the following:

SPI_execute_with_args executes a command that might include references to externally supplied parameters. The command text refers to a parameter as $n, and the call specifies data types and values for each such symbol. read_only and count have the same interpretation as in SPI_execute.

The main advantage of this routine compared to SPI_execute is that data values can be inserted into the command without tedious quoting/escaping, and thus with much less risk of SQL-injection attacks.

The SQL script looks like the following (If I replace the $1 with a real schema name manually and run it as a normal script, everything works as it should):

CREATE SCHEMA $1;
ALTER SCHEMA $1 OWNER TO some_user;

CREATE FUNCTION $1.foo() ...

CREATE TABLE $1.bar ...
...

But now I want to run it from the C code, and as the documentation lacks any working examples on SPI, I had to google around to find anything that could guide me further. And I found this example on SO where the function looks like this:

...

Datum 
foo(PG_FUNCTION_ARGS)
{
    int ret;
    Datum args[1];
    Oid argtypes[1] = { INT4OID };
    Datum result;
    bool isnull;

    SPI_connect();

    args[0] = PG_GETARG_INT32(0);

    /* ensure expected result type by casting */
    ret = SPI_execute_with_args("SELECT ($1 + 10)::int", 
                                   1, argtypes, args, NULL,
                                   true, 1);

    Assert(SPI_processed == 1);

    result = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull);
    Assert(!isnull);

    SPI_finish();

    PG_RETURN_DATUM(result);
}

...

This works as it should (replaces $1 with a number entered as a parameter).

But as soon I start to modify it to work with my own query, everything breaks. I can't even make it work with only the first line of the query.

I have, for the record, also tried to just run a simple SELECT '$1' query and replace it with various variables. But nothing else than the example works. Either the server crashes, returns invalid syntax at $1 or just returns $1 as the answer.

---

If I'm right it seems like it matters where and what you want to replace $1 with. And that SPI doesn't just do a "find and replace" on the $1?

I have tried some different OID:s when testing various variable types, like: ANYOID, CSTRINGOID, CHAROID, REGNAMESPACEOID, TEXTOID etc. And I have tried to both send the variable as a pure char array and as a pointer to a text block allocated with SPI_palloc() or palloc(). But no success...

Example code that I've put together from the examples and documentation I've found:

PG_FUNCTION_INFO_V1(foobar);
Datum foobar(PG_FUNCTION_ARGS)
{
    Datum arguments[1];
    Oid argument_types[1] = { ANYOID };
    Datum result;
    bool isnull;
    arguments[0] = "some_text";

    SPI_connect();
    SPI_execute_with_args("SELECT '$1'", 1, argument_types, arguments, NULL, false, 0);
    result = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull);
    SPI_finish();

    PG_RETURN_DATUM(result);
}

When running this code I get the following result:

SELECT foobar();
 foobar
--------
 $1
(1 row)

---

I'm not sure that this is the best way to do this, but even if it isn't, it would be nice to know more of how this SPI function works as I will need it further into the project.

Does anybody have any working examples to this or something to push me into the right direction?

Answer 1

The $1 in your SELECT '$1' is inside single quotes, so it is a string literal and not a parameter.

Use the following instead:

SELECT $1

Note that you can use a parameter where you could also use a literal of the same type, but you cannot use a parameter for an identifier like a table or column name.

If you need a variable in such a place, you'll have to construct a query string using snprintf.

To avoid SQL injection, use quote_identifier from utils/builtins.h.

---

Here is a fixed version of your code:

#include "postgres.h"
#include "fmgr.h"
#include "catalog/pg_type.h"
#include "executor/spi.h"
#include "utils/builtins.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(foobar);
Datum foobar(PG_FUNCTION_ARGS)
{   
    Datum arguments[1];
    Oid argument_types[1] = { TEXTOID };
    char *res;
    bool isnull;
    Datum result;
    /* for when we don't want to use the SPI context */
    MemoryContext context = CurrentMemoryContext;

    arguments[0] = CStringGetTextDatum("some_text");

    SPI_connect();

    SPI_execute_with_args("SELECT $1", 1, argument_types, arguments, NULL, false, 0); 

    result = SPI_getbinval(SPI_tuptable->vals[0], SPI_tuptable->tupdesc, 1, &isnull);

    res = MemoryContextStrdup(context, TextDatumGetCString(result));

    SPI_finish();

    PG_RETURN_TEXT_P(CStringGetTextDatum(res));
}