Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    11

    Unanswered: How to escape $ signs in plpgsql function?

    Is there any way to use $BODY$ in the string? If I am renaming it to $BODY1$ or any other - it is working, but can I use $BODY$ somehow?

    Code:
    CREATE OR REPLACE FUNCTION test_function()
      RETURNS character varying AS
    $BODY$
    DECLARE
            dyn_sql    varchar(5000):='';
    BEGIN
     dyn_sql = '$BODY$';
    return dyn_sql;
    END
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    ERROR: unterminated quoted string at or near "';
    return dyn_sql;
    END
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;

    "
    LINE 8: dyn_sql = '$BODY$';
    ^

    ********** Error **********

    ERROR: unterminated quoted string at or near "';
    return dyn_sql;
    END
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;

    "
    SQL state: 42601
    Character: 153

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You can use anything for $BODY$, so the following should work:
    Code:
    CREATE OR REPLACE FUNCTION test_function()
      RETURNS character varying AS
    $PROC$
    DECLARE
            dyn_sql    varchar(5000):='';
    BEGIN
     dyn_sql = '$BODY$';
    return dyn_sql;
    END
    $PROC$
      LANGUAGE plpgsql VOLATILE
      COST 100;

  3. #3
    Join Date
    Jun 2011
    Posts
    11
    I know that. But can I use $BODY$ in both places?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •