CODEWITHSUNDEEP
sqlsql-serversql-server-2008postgresqlt-sql
atapaka
atapaka

Reputation: 1360

call an sql function by name returned by select

I would like to find out how to call a function the name of which is returned from a select query. So let's say, I have a select query:

SELECT function_name FROM functions WHERE id=1;

Now let's say, the returned functions name is fce1 and now I want to execute:

SELECT fce1(parameters);

Now my initial idea would be:

SELECT CONCAT(SELECT function_name FROM functions WHERE id=1;, "(params)");

I am quite certain that the idea is wrong. But I was trying to figure that out some time ago and I remember that at least MS SQL was able to achieve my goal and also POSTGRESQL. Anyway, neither do I remember or am I able to find how to do it. Ideas would be appreciated.

Upvotes: 0

Views: 1414

Answers (2)

user330315
user330315

Reputation:

Assuming all functions return the same data type, you could build a wrapper function in Postgres that you pass the ID of the function to be called:

create or replace function call_func(p_id integer)
  returns integer
as
$$
declare 
  l_result integer;
  l_name   text;
  l_params text;
begin 
  select function_name, parameters
    into l_name, l_params
  from functions
  where id = p_id;

  execute 'select '||l_name||'('||l_params||')'
    into l_result;

  return l_result;    
end;
$$
language plpgsql;

Note: the above is just an example.

It's wide open to SQL injection and does not do any error checking or sanitizing the parameters! But it might point you into the right direction.

Assume you have the functions:

create or replace function foo(p_arg_1 integer, p_arg_2 integer)
  returns integer
as
$$
  select p_arg_1 + p_arg_2;
$$
language sql;

create or replace function bar(p_value integer)
  returns integer
as
$$
  select p_value * 4;
$$
language sql;

And the functions table looks like this:

id | function_name | parameters
---+---------------+-----------
 1 | fce           | 42        
 2 | foo           | 1,2

You can then do 

select call_func(2);

or

select call_func(1);

But again: this will only work if all functions return the same result. e.g. scalar functions returning a single value, or set returning functions returning the same table definition.

Upvotes: 0

Metaphor
Metaphor

Reputation: 6415

DECLARE @func NVARCHAR(50);

SELECT @func = function_name FROM functions WHERE id=1;

EXEC ('select ' + @func + '()')

Upvotes: 2

Related Questions