When I execute the below postgres function in pgadmin, it works fine but when run through liquibase code, it throws the error:
Unterminated dollar quote started at position 274 in SQL CREATE OR REPLACE FUNCTION
Can someone please help what am I missing here? tia
Postgres version: 42.2.15
CREATE OR REPLACE FUNCTION find_data(param_bic character varying, param_countrycode character varying, param_nationalid character varying, param_iban_countrycode character varying, param_iban character varying, ref1 refcursor)
RETURNS refcursor
AS $BODY$
declare select_statement text default 'select bic.bic, bic.chips_uid, bic.national_id, bic.institution_name, bic.pob_number,
bic.street_address_1, bic.street_address_2, bic.street_address_3, bic.street_address_4, bic.city, bic.cps, bic.zip_code,
bic.country_name, bic.iso_country_code, iban.iban_bic, bic.record_key
from swift_ref_bic bic left join swift_ref_iban iban on iban.record_key = bic.iban_key ';
WHERE_STATEMENT text DEFAULT ' WHERE 1=1 ';
begin
IF PARAM_IBAN is not null
THEN WHERE_STATEMENT := CONCAT(WHERE_STATEMENT , ' AND iban.IBAN_NATIONAL_ID like ' , '''' , PARAM_IBAN , '%' , '''');
END IF;
IF PARAM_NATIONALID is not null
THEN WHERE_STATEMENT := CONCAT(WHERE_STATEMENT , ' AND bic.NATIONAL_ID like ' , '''' , PARAM_NATIONALID , '%' , '''');
END IF;
IF PARAM_IBAN_COUNTRYCODE is not null
THEN WHERE_STATEMENT := CONCAT(WHERE_STATEMENT , ' AND iban.IBAN_ISO_COUNTRY_CODE like ' , '''' , PARAM_IBAN_COUNTRYCODE , '%' , '''');
END IF;
IF PARAM_COUNTRYCODE is not null
THEN WHERE_STATEMENT := CONCAT(WHERE_STATEMENT , ' AND bic.ISO_COUNTRY_CODE like ' , '''' , PARAM_COUNTRYCODE , '%' , '''');
END IF;
IF PARAM_BIC is not null
THEN WHERE_STATEMENT := CONCAT(WHERE_STATEMENT , ' AND bic.BIC like ' , '''' , '%' , PARAM_BIC , '%' , '''');
ELSEIF PARAM_NATIONALID IS NOT NULL AND PARAM_COUNTRYCODE IS NULL AND PARAM_IBAN_COUNTRYCODE IS NULL AND PARAM_IBAN IS NULL THEN WHERE_STATEMENT := ' WHERE 1 != 1 ';
ELSEIF PARAM_NATIONALID IS NULL AND PARAM_COUNTRYCODE IS NULL AND PARAM_IBAN_COUNTRYCODE IS NULL AND PARAM_IBAN IS NULL THEN WHERE_STATEMENT := ' WHERE 1 != 1 ';
END IF;
SELECT_STATEMENT := CONCAT (SELECT_STATEMENT, WHERE_STATEMENT) ;
open ref1 for execute (SELECT_STATEMENT);
return ref1;
END;
$BODY$
LANGUAGE plpgsql ;