Re: Calling a SQL function inside a C function

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(dot)stehule(at)gmail(dot)com>
wrote:

> Hi
>
> čt 27. 8. 2020 v 0:43 odesílatel Eric Zhu <erkangzhu(at)gmail(dot)com> 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
>>
>