Table condition: This table contains function name in the column fn_name
create table condition(id int,name varchar(50),fn_name varchar(50));
Data:
insert into condition values(1,'check id','fn_condition_1');
insert into condition values(2,'check name','fn_condition_2');
insert into condition values(3,'check salary','fn_condition_3');
Function: fn_condition_1
create or replace function fn_condition_1(p_id int)
returns boolean
as
$$
begin
if(select count(1) from emp where id = p_id) > 0
then
return 'true';
else
return 'false';
end if;
end
$$
language plpgsql;
Procedure: prc_bsn_condition within this procedure calling function.
create or replace procedure prc_bsn_condition(p_id int)
language plpgsql
as
$$
declare v_out varchar(10);
v_sql varchar(200);
v_fn varchar(20);
begin
select * into v_fn from (select fn_name from condition where id = p_id);
v_sql := 'SELECT * FROM '||v_fn||'('||p_id||')';
execute v_sql into v_out;
raise info '%',v_out;
end
$$;
Output:
true;
Note: The above procedure works fine, but I would like to convert the dynamic sql to the static
due to the fear of sql injections. Or is there any other method to call or execute those functions
which are stored in the table.
1
A combination of format() to create the SQL statement and USING for getting the content in that statement, will fix the SQL injection risk:
CREATE OR REPLACE PROCEDURE prc_bsn_condition(p_id INT)
LANGUAGE plpgsql
AS
$$
DECLARE
v_out VARCHAR(10);
v_sql VARCHAR(200);
v_fn VARCHAR(20);
BEGIN
SELECT fn_name
INTO v_fn
FROM condition
WHERE id = p_id;
v_sql := FORMAT('SELECT * FROM %I($1)', v_fn);
EXECUTE v_sql
INTO v_out
USING p_id;
RAISE INFO '%',v_out;
END
$$;