Call function from concatinated strings

Question

I have two strings:

'UPP'
'ER'

And the value, 'a' for example.

I need to call the function UPPER (synthesized from the two strings) and pass the value as parameter.
How can I do this?

( In reality, I need to concat the function crosstab with the counter of columns... )

Seriously? You can make this a bit harder if you use ascii values... — PinnyM, Feb 05 '13 at 17:03
@PinnyM: Is just one example, solving it, I solve a big problem. — FabianoLothor, Feb 05 '13 at 17:04
@E.T.: Yes, I need call the function crosstabN dynamically according with the number of columns necessary. Solving the above case, I solve my problem. — FabianoLothor, Feb 05 '13 at 17:10
To execute this as dynamic SQL you also need to define the *return type* of the function. — Erwin Brandstetter, Feb 05 '13 at 18:00

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

A stub for a function utilizing dynamic SQL could look like this:

CREATE OR REPLACE FUNCTION f_exec(text, text, text, OUT result text)
  RETURNS text AS
$func$
BEGIN
    EXECUTE 'SELECT ' || $1  || $2 || '($1)' -- last $1 not referring func param
    INTO   result
    USING  $3;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_exec('up', 'per', 'a')

Don't get confused, $1 has its own scope inside EXECUTE and refers to the parameter passed in by the USING clause, not to the outer function parameter.

More background in this related answer or in the chapter Executing Dynamic Commands in the manual.

Warning

This opens you up for SQL injection!
Read the advice under this related question on dba.SE and and the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.

There is a smart way to avoid SQLi in this special case:

CREATE OR REPLACE FUNCTION f_exec(
   func1 text
  ,func2 text
  ,param text
  ,OUT result text) AS
$func$
DECLARE
   funcname text := (func1 || func2)::regproc;
BEGIN
   EXECUTE 'SELECT ' || funcname || '($1)::text'
   INTO   result
   USING  param;
END
$func$ LANGUAGE plpgsql;

The cast to the object identifier type regproc effectively weeds out all attempts to inject anything but a valid function name via $1 and $2. The USING clause already neutralizes attempts to inject code via $3 (param).

Also:

Use parameter names to make it less confusing.
Omit the RETURNING clause from the function definition, since the OUT parameter takes care of it.
Cast the result of the function to text since we don't know the actual return type of the function and every data type in Postgres can be cast to text.
The function still need to accept a single IN parameter of type text or you get an exception.

Call function from concatinated strings

1 Answers1

Warning