CODEWITHSUNDEEP
sqlcdatabasepostgresqlplpgsql
Kungi
Kungi

Reputation: 1536

Function as parameter to another function in 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?

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?

Upvotes: 10

Views: 5218

Answers (4)

jian
jian

Reputation: 4824

--create an being parametered function.

 CREATE OR REPLACE FUNCTION public.select1()                                                                                       RETURNS integer
      LANGUAGE sql
      IMMUTABLE
     AS $function$ select 10;
     $function$

--create an function with function as input parameter.

CREATE OR REPLACE FUNCTION public.func_func(fn_name text)
 RETURNS SETOF parent_tree
 LANGUAGE plpgsql
AS $function$
begin
RETURN QUERY EXECUTE
format('select * from parent_tree where parent_id =  %s::regprocedure', fn_name);
end
$function$

--Call it.

select * from func_func('select1()');

Upvotes: 1

Nimeshka Srimal
Nimeshka Srimal

Reputation: 8920

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.

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.

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.

Hope it will help someone!

Upvotes: 4

A.H.
A.H.

Reputation: 66243

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.

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

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.

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

Upvotes: 9

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143071

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

Upvotes: 2

Related Questions