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$`
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:
RETURN SETOF ...or like I fixed it.