Question 89643 in PostgreSQL Question posted in
The official documentation can be found here.

I need to be able to execute a function or procedure dynamically, the name of which will be pulled from another table – Postgresql

PostgreSQL
I have a selection criteria, based on parameters I know that for this particular item it needs to call public.dothisprocedure another one could be public.dothatprocedure. I have looked at the Execute, but the that seems to execute selects or update sql statements only. I guess I want kind of Call Execute Call myUri() but it’s trying to fire the myUri procedure which of course doesn’t exist. Is there something like Eval ?

Tried Execute, not what I am expecting Tried Call myUri but wrong target… if someone has a clue or can point me in the right direction I would be most greatful.. Thanks

for example in T-SQL it’s just if @uri is the target procedure…
EXEC @uri @dt_from = @que_from_utc, @dt_to = @que_to_utc;

but I can’t seem to find anyway to do that in postgre ( I know how to add the parameters..) just need to know how to EXEC @uri..

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Figured it out simpler in the end

    EXECUTE 'CALL ' || planUri || '();';

    it was just the CALL inside the string that I was missing.

    But what you have is similar also, think I prefer your option, very similar to what I am trying to do in fact

    I have a temporary table which is filled by a list of calculations plans.

    I iterate through the list, and check if there is a uri ( procedure ) for that plan. If there is , then I call the procedure, once processed its removed from the list.

    Now that this piece is in place, obv simple enough to for Functions and passing parameters in, just happy that this piece is working . Thanks


  2. 0

    I don’t sure, but I have example from of my projects, I have list of procedures in management table, and then I run them from other procedure one by one.

     CREATE OR REPLACE PROCEDURE stg.sp_transform_tables()
 LANGUAGE plpgsql
AS $procedure$
DECLARE 
temprow stg.transform_management%rowtype;
current_procedure varchar(255);
curr_start timestamp ;
curr_end timestamp ;
curr_duration integer;
tmi varchar(10000) ;
BEGIN

EXECUTE 'call stg.sp_exchange_rates()';

FOR temprow IN
        select * from  stg.transform_management order by run_order
    LOOP        
        current_procedure := temprow.procedure_name;
        select now() into curr_start;
        EXECUTE FORMAT('CALL %s',current_procedure);
        tmi:=FORMAT('CALL %s',current_procedure);
        select now() into curr_end;
        select  EXTRACT(EPOCH FROM (curr_end - curr_start)) AS difference into curr_duration;
        EXECUTE FORMAT('UPDATE stg.transform_management set last_run= %s%s%s,last_duration=%s where procedure_name= %s%s%s',CHR(39),curr_end,CHR(39),curr_duration,CHR(39),current_procedure,CHR(39));
        raise notice '%,%,%',current_procedure,'Execution Done',E'n';
        raise notice '%,%,%',tmi,'Exec',E'n';

    END LOOP;

END;
$procedure$
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search