Re: Calling a SQL function inside a C function

[Eric Zhu <erkangzhu@xxxxxxxxx>]

Much thanks! This is exactly what I was looking for:

funcname = stringToQualifiedNameList("times_two");

funcoid = LookupFuncName(func_name, 1, funcargs, false);

Datum ret = OidFunctionCall1(funcoid, Int32GetDatum(13));

Eric

On Wed, Aug 26, 2020 at 8:53 PM Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:

Hi

čt 27. 8. 2020 v 0:43 odesílatel Eric Zhu <erkangzhu@xxxxxxxxx> napsal:

How do I call a function defined using CREATE FUNCTION in SQL inside a C function in an extension? I feel this should be possible as the query parser is able to resolve the function names and arguments in a raw string query. I want to know if there is a standard way to look up for user-defined functions in the backend.

For example, I have a function defined in SQL:

```

CREATE FUNCTION times_two(x integer)

RETURNS integer AS $$

SELECT x*2

$$ LANGUAGE SQL;

```

Now I wish to call `times_two()` in a C extension similar to:

```

// Look up for the user-defined function times_two()

// ...

// Use the function.

Datum ret = DirectFunctionCall(times_two, Int32GetDatum(13));

```

Surely, it is not possible. You can use SPI interface, which is most simple  https://www.postgresql.org/docs/current/spi.html and instead of calling function, you can call "SELECT  times_two(x)". PLpgSQL is working like that. It ensures necessary checks, and you don't need to manually handle NULL values.

For direct function call of SQL function, you can use OidFunctionCall

some like

Oid funcargs = {INT4OID};

List *funcname;

Oid funcoid

funcname = stringToQualifiedNameList("times_two");

funcoid = LookupFuncName(func_name, 1, funcargs, false);

Datum ret = OidFunctionCall1(funcoid, Int32GetDatum(13));

Attention - for direct function calls the function should not to have any NULL argument, and should not to return NULL.

Regards

Pavel

Best,

Eric