Create function in dynamic sql PostgreSQL

Question

Is it possible to create a function or execute anonymous block inside dynamic SQL in PostgreSQL? I'm looking for something like this:

Create or replace FUNCTION fff(p1 int)
LANGUAGE  plpgsql
  AS $$
  DECLARE
   v_Qry  VARCHAR(4000);
  BEGIN
    v_Qry := '
    Create or replace FUNCTION fff_DYNAMIC_SQL()
    LANGUAGE  plpgsql
    AS $$
    DECLARE
    v1  INTEGER;
    begin
     v1 := ' || p1 || ';
     RETURN;
    END; $$;';
   EXECUTE v_Qry;
   RETURN;
END; $$;

Answer 1

You have three levels of nested string in your code. The best way to deal with that, is to use dollar quoting for all of them. When creating dynamic SQL it's also better to use format() instead of string concatenation. Then you only need a single string with placeholders which makes the code a lot easier to read.

To nest multiple dollar quoted strings use a different delimiter each time:

Create or replace FUNCTION fff(p1 int)
  returns void
  LANGUAGE  plpgsql
AS
$$ --<< outer level quote
DECLARE
 v_Qry  VARCHAR(4000);
BEGIN
  v_Qry := format(
$string$ --<< quote for the string constant passed to the format function
    Create or replace FUNCTION fff_DYNAMIC_SQL()
       returns void
       LANGUAGE  plpgsql
    AS 
    $f1$ --<< quoting inside the actual function body
    DECLARE
      v1  INTEGER;
    begin
      v1 := %s;
      RETURN;
    END; 
    $f1$
$string$, p1);
  EXECUTE v_Qry;
  RETURN;
END; 
$$;

You also forgot to declare the returned data type. If the function does not return anything, you need to use returns void.