Error: Unterminated dollar quote started at position <> in SQL CREATE OR REPLACE FUNCTION

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 ;
1 Like

@enjoy_life could you be a little more specific on the part “when run through liquibase code”.
How exactly is that script included in the Liquibase deploy (as a plain .sql file/script, as a Liquibase formatted SQL changeset (inside a .sql file) or as a sql changeset type)? An example/sample would help.

Thanks,
Eduard

It is being executed as a SQL changeset (inside a .sql file)

-- changeset kajal:148

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 ;

@enjoy_life could you try with having the first two lines of the .sql script as below:

--liquibase formatted sql
--changeset kajal:148

notice there are no space between the – and the first character of the two lines. This is intended.

No, it didn’t work as
--liquibase formatted sql
was already present at the very top of the script (my bad, missed mentioning it)

I got it sorted by adding the below:

-- changeset kajal:148 endDelimiter://

Thanks , apreciate your help

2 Likes

Thank you @enjoy_life , your solution worked. It helped me. thank you so much