liquibase sql migration issue-CodePudding

I am writing 1 PostgreSQL function for some operation. Writing SQL migration for that function but facing formatting error as liquibase is not able to recognize some portion.

Function Liquibase Migration:

CREATE OR REPLACE FUNCTION schema.fncn(trId integer, sts integer, stIds character varying)
 RETURNS double precision
 LANGUAGE plpgsql
AS '
DECLARE
      abc integer;
      query CHAR(1500);
      xyz   integer;
BEGIN
      query := ''select sum(t.a) 
      FROM schema.tbl t 
      where t.id in(1,2) 
      and t.status ='' || sts || 
      '' and t.status <> 2 
      and t.tr_id ='' || trId || 
      '' and t.sw in('''', ''N'')'';

      IF stIds is not null then
        query := query || '' AND t.st_id IN ('' || stIds || '')'';
      ELSE
      END IF;

      EXECUTE query INTO abc;

      SELECT abc INTO xyz;
    RETURN xyz;
END;
'
;

Following error it throwing:

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "N"

Reason: liquibase.exception.DatabaseException: ERROR: syntax error at or near "N"

Any suggestion what I am missing?

CodePudding user response：

The immediate problem is the nesting of ' of single quotes. To make that easier, use dollar quoting for the function body. You can nest dollar quoted string by choosing different delimiters.

To avoid any problems with concatenation of parameters, use parameter place holders in the query and pass the values with the USING clause. That will however require two different execute calls.

I assume stIds is a comma separated string of values. To use that as a (single) placeholder, convert it to an array using string_to_array() - or even better: change the type of the input parameter to text[] and pass an array directly.

The query variable is better defined as text, don't use char. There is also no need to copy the result of the query into a different variable (which by the way would be more efficient using xyz := abc; rather than a select into)

CREATE OR REPLACE FUNCTION schema.fncn(trId integer, sts integer, stIds character varying)
 RETURNS double precision
 LANGUAGE plpgsql
AS
$body$
DECLARE
      abc integer;
      query text;
BEGIN
  query := $q$ select sum(t.a) 
            FROM schema.tbl t 
            where t.id in (1,2) 
            and t.status = $1 
            and t.status <> 2 
            and t.tr_id = $2
            and t.sw in ('''', 'N') $q$;

  IF stIds is not null then
    query := query || $sql$ AND t.st_id = ANY (string_to_array($4, ',') $sql$;
    EXECUTE query INTO abc
      using trid, sts, stids;
  ELSE
    EXECUTE query INTO abc
      using trid, sts;
  END IF;
      
  RETURN abc;
END;
$body$
;

Note that in the Liquibase change, you must use splitStatements=false in order to run this without errors.