PostgreSQL Call a stored procedure with its name is into variable-CodePudding

I want to call a stored procedure whose name is in a variable with this code :

    DECLARE
      spName text;
      varIn1 text;
      varIn2 text;
      varOut text;
      varSQLQuery text;
    BEGIN
    spName := 'myProc';
    varIn1 := 'Value1';
    varIn2 := 'Value2';
    varSQLQuery := format('CALL myschema.%s(varIn1,varIn2,varOut);',spName,);
    RAISE NOTICE 'varSQLQuery %', varSQLQuery;
    EXECUTE varSQLQuery;
    RAISE NOTICE 'varOut %', varOut;

The printed query from varSQLQuery works if I copy and paste them inside the code. But it doesn't work in EXECUTE function.

The error is : "column "varIn1" does not exist".

I also tried this :

varSQLQuery := format('CALL myschema.%s(varIn1,varIn2,varOut);',spName);
EXECUTE IMMEDIATE varSQLQuery;

varSQLQuery := format('
  BEGIN
  CALL myschema.%s(varIn1,varIn2,varOut);
  END;'
,spName);
EXECUTE varSQLQuery;

varSQLQuery := format('myschema.%s(varIn1,varIn2,varOut);',spName);
EXECUTE varSQLQuery;

varSQLQuery := format('myschema.%s(varIn1,varIn2,varOut);',spName);
CALL varSQLQuery;

I have no more new ideas.

So, how do you call a stored procedure with a name in a variable, with (or without?) the EXECUTE function?

CodePudding user response：

Finally, I succeeded to get the output variable with the INTO clause in the following code:

varSQLQuery := format('CALL myschema.%s($1,$2,$3);', spName);
RAISE NOTICE 'varSQLQuery %', varSQLQuery;
EXECUTE varSQLQuery INTO varOut USING varIn1, varIn2, varOut;

CodePudding user response：

The problem is that dynamic SQL does not 'see' the variables as it does not run in the same block context (documentation). Try to do it like this:

varSQLQuery := format('CALL myschema.%s($1,$2,$3);', spName);
RAISE NOTICE 'varSQLQuery %', varSQLQuery;
EXECUTE varSQLQuery using varIn1, varIn2, varOut;

However the procedure's output parameter will not work as expected. The value of varOut will not change. It may be a better idea to use a function instead of a procedure.