How to escape $ signs in plpgsql function?

sidukas · #1 (**permalink**) 06-28-11, 04:56

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

shammat · #2 (**permalink**) 06-28-11, 15:06

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;

sidukas · #3 (**permalink**) 06-29-11, 03:49

I know that. But can I use $BODY$ in both places?

Internet Services

Web Development

Digital Marketing

Consumer Tech