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?
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 differentexecute
calls.I assume
stIds
is a comma separated string of values. To use that as a (single) placeholder, convert it to an array usingstring_to_array()
- or even better: change the type of the input parameter totext[]
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 usingxyz := abc;
rather than aselect into
)Note that in the Liquibase change, you must use
splitStatements=false
in order to run this without errors.