How to avoid syntax error when returning text which includes a BEGIN statement in it?

Question

I'm trying to build a function that returns some text. The text includes a BEGIN statement which seems to cause an error:

CREATE OR REPLACE FUNCTION create_trigger() RETURNS TEXT AS $$
BEGIN
    RETURN
    'CREATE OR REPLACE FUNCTION update_view() RETURNS trigger AS $$
       BEGIN -- error is near this begin
        IF TG_OP = ''INSERT''  THEN DO SOMETHING;
        ELSIF TG_OP = ''UPDATE'' THEN DO SOMETHING;
        END IF;
        RETURN NEW;
       END;
    $$ language plpgsql';
END;
$$ language plpgsql

I get a syntax error near the word BEGIN in the return statement and don't understand why, as I'm only trying to return some text. Is this because plpgsql recognizes the word BEGIN even if it is in a string portion of the code?

Does not work either (edited the post with this modification) — kaycee, Dec 21 '16 at 16:31
The begin isn't causing the error, it's the nested `$$`. Use a different delimiter for the quoted function. — alzee, Dec 21 '16 at 16:34
Oh it works when using $BODY$ in the quoted function instead of $$. Post this as an answer ! — kaycee, Dec 21 '16 at 16:35
About quoting and dollar-quotes: http://stackoverflow.com/questions/12316953/insert-text-with-single-quotes-in-postgresql/12320729#12320729 — Erwin Brandstetter, Dec 23 '16 at 01:40

score 0 · Answer 1 · edited May 23 '17 at 11:45

Like user3137702 commented, the problem is with the dollar-quotes. I'd also suggest some other simplifications:

CREATE OR REPLACE FUNCTION create_trigger()
  RETURNS text AS
$func$
SELECT
   $f$CREATE OR REPLACE FUNCTION update_view()
        RETURNS trigger AS
      $func1$
      BEGIN
       CASE TG_OP
         WHEN 'INSERT' THEN -- DO SOMETHING;
         WHEN 'UPDATE' THEN -- DO SOMETHING;
       END CASE;
       RETURN NEW;
      END;
      $func1$ LANGUAGE plpgsql$f$;
$func$  LANGUAGE sql IMMUTABLE;

About quoting and nested dollar-quotes:

Insert text with single quotes in PostgreSQL

How to avoid syntax error when returning text which includes a BEGIN statement in it?

1 Answers1