How to insert function argument in a $$ $$ string in Postgresql?-CodePudding

So I have a function with an argument for example:

CREATE OR REPLACE FUCNTION example(arg int)
RETURNS int4
LANGUAGE plpgsql
AS $$

   BEGIN
   --function body
   somequery := $q1$ SELECT field*arg FROM randomtable  $q1$
   EXECUTE somequery;
   END

   $$

Right now arg is just a string, how do I pass real arg from function argument ?

CodePudding user response：

For your example, you don't need dynamic SQL to begin with, but if you really do, use a parameter placeholder:

CREATE OR REPLACE FUCNTION example(arg int)
  RETURNS int4
LANGUAGE plpgsql
AS $$
declare
  somequery text;
  result int;
BEGIN
   somequery := 'SELECT field*$1 FROM randomtable';

   EXECUTE somequery
     into result
     using arg;

   return result;
END;
$$

But without more information about the real problem you are trying to solve, the above can be simplified to:

CREATE OR REPLACE FUCNTION example(arg int)
  RETURNS int4
LANGUAGE plpgsql
AS $$
declare
  result int;
BEGIN
   SELECT field * arg 
     into result
   FROM randomtable;

   return result;
END;
$$