作为Postgres中另一个函数的参数

Can I create a user defined function in Postgres either through the C-Language Function API or by using pl/pgsql which accepts a callback function as parameter?

我可以通过C语言函数API或使用接受回调函数作为参数的pl / pgsql在Postgres中创建用户定义的函数吗?

As far as I see there is no way to do this through the C-Language API since it only accepts sql datatypes and there is no datatype for function. But maybe I'm missing something?

据我所知,没有办法通过C语言API执行此操作,因为它只接受sql数据类型,并且没有函数的数据类型。但也许我错过了什么?

3 个解决方案

#1

I think you can't, but since there are no anonymous functions, passing function name should do.

我想你不能,但由于没有匿名函数,传递函数名应该做。

#2

Since each function / procedure must have an entry in pg_proc, you can use the primary key for identifying the procedure. This would also eliminate the problems with procedures having the same name but different number of parameters or different parameter types.

由于每个函数/过程必须在pg_proc中有一个条目,因此可以使用主键来标识过程。这也将消除具有相同名称但不同数量的参数或不同参数类型的过程的问题。

Shorthands for this are the types regproc and regprocedure with the associated casts for easier handling. Lookup the manual for these.

对此的缩写是regproc和regprocedure类型以及相关的强制转换,以便于处理。查找这些手册。

Identifying the function and passing it around is no problem:

识别函数并传递它是没有问题的:

select 'pg_database_size(oid)'::regprocedure; -- create "reference"
     regprocedure      
-----------------------
 pg_database_size(oid)

Use regprocedure as the parameter type.

使用regprocedure作为参数类型。

The problem I did not yet figure out is how to actually call such a thing in a convenient way.

我还没弄清楚的问题是如何以一种方便的方式实际调用这样的东西。

#3

Old question and already has an accepted answer. But it doesn't clearly explain how to do this. So I thought of adding a more clear answer.

老问题已经有了一个接受的答案。但它没有明确解释如何做到这一点。所以我想添加一个更清晰的答案。

Let's assume you pass the callback function's name to your main function as a varchar value.

假设您将回调函数的名称作为varchar值传递给main函数。

CREATE OR REPLACE FUNCTION public.get_function_fields(fn_name character varying)
    ...
    ...

Now if you want to call this fn_name function inside a query, you need to use EXECUTE command, and properly cast your function name using regproc as below.

现在,如果要在查询中调用此fn_name函数,则需要使用EXECUTE命令,并使用regproc正确转换函数名称,如下所示。

EXECUTE 'create temp table if not exists temp_call as select * from ' || fn_name::regproc || '() limit 1';

Important part is this: ...|| fn_name::regproc || '()... As you can see, you have to append the parenthesis and cast the function name with ::regproc.

重要的是这个:... || fn_name :: regproc || '()...如您所见,您必须附加括号并使用:: regproc强制转换函数名称。

Hope it will help someone!

希望它会帮助别人!

#1