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?

• Luan Huynh: thanks, It's the first time I know the way to create function in function. I think your second query has a small problem. When running "select func_test() " , it raises an error: "ERROR: cannot use RETURN QUERY in a non-SETOF function. LINE 3: RETURN QUERY" . If I uncomment "RUN QUERY" clause, the query "select func_test()" works fine but the query "select func_test2()" got an error "ERROR: query has no destination for result data".
• Erwin Brandstetter: @lnnnh: Right, either make that RETURN SETOF ...or like I fixed it.