Create a function from another function

Question

Is it possible to create a new function from another plpgsql function? Something like this:

CREATE OR REPLACE FUNCTION func_test()
RETURNS VOID AS
$BODY$
BEGIN
  CREATE OR REPLACE FUNCTION func_test2()
  RETURNS INT AS
  BEGIN
  $$
    SELECT 1;
  $$
  END
END
$BODY$
LANGUAGE plpgsql;

When I execute the code above I get:

ERROR:  unexpected end of function definition at end of input
LINE 13: $BODY$`

Answer 1

Yes, totally possible. You just have some random syntax errors.

And I took the freedom to replace the term "stored procedure" in your query with "function", since Postgres does not have stored procedures. Just functions - doing almost, but not quite, the same.

This would work:

CREATE OR REPLACE FUNCTION func_test()
  RETURNS VOID AS
$func$
BEGIN
  CREATE OR REPLACE FUNCTION func_test2()
     RETURNS INT AS
  $$
    SELECT 1
  $$ LANGUAGE sql;
END
$func$ LANGUAGE plpgsql;

Or:

CREATE OR REPLACE FUNCTION func_test()
  RETURNS VOID AS
$func$
BEGIN

CREATE OR REPLACE FUNCTION func_test2()
  RETURNS INT AS
$$
BEGIN
   RETURN (SELECT 1);
END
$$ LANGUAGE plpgsql;

END
$func$ LANGUAGE plpgsql;

Compare:

• Can I make a plpgsql function return an integer without using a variable?