3

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$`
5

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:

  • 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". – Luan Huynh Dec 8 '15 at 1:44
  • @lnnnh: Right, either make that RETURN SETOF ...or like I fixed it. – Erwin Brandstetter Dec 8 '15 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.