Unterminated dollar quote started

General Discussion
1

Hello,
i am testing liquibase for the 1st time (i often use flyway, but i just wanted to test liquibase).

First script:
myfile.sql

--liquibase formatted sql  
--changeset bob:1 

CREATE OR REPLACE FUNCTION week_to_date(
  IN in_year INTEGER,
  IN in_week INTEGER,
  IN in_dow INTEGER DEFAULT NULL
)
RETURNS DATE AS 
$$
BEGIN
  RETURN to_timestamp('1 ' || in_year, 'IW IYYY')::DATE + (COALESCE(in_dow, 1) + 6) % 7 + 7 * in_week - 7;
END;
$$ LANGUAGE plpgsql;

my cmd:

liquibase --driver=org.postgresql.Driver --classpath=postgresql-42.2.2.jar --url="jdbc:postgresql://localhost:60901/mydb"  --username=myusey --password=mypwd --schemas=myschema --changeLogFile=myfile.sql migrate

result:

Unexpected error running Liquibase: Unterminated dollar quote started at position 137 in SQL CREATE OR REPLACE FUNCTION week_to_date(
  IN in_year INTEGER,
  IN in_week INTEGER,
  IN in_dow INTEGER DEFAULT NULL
)
RETURNS DATE AS 
$$
BEGIN
  RETURN to_timestamp('1 ' || in_year, 'IW IYYY')::DATE + (COALESCE(in_dow, 1) + 6) % 7 + 7 * in_week - 7
END. Expected terminating $$ [Failed SQL: (0) CREATE OR REPLACE FUNCTION week_to_date(
  IN in_year INTEGER,
  IN in_week INTEGER,
  IN in_dow INTEGER DEFAULT NULL
)
RETURNS DATE AS 
$$
BEGIN
  RETURN to_timestamp('1 ' || in_year, 'IW IYYY')::DATE + (COALESCE(in_dow, 1) + 6) % 7 + 7 * in_week - 7
END]
For more information, please use the --logLevel flag

i’ve been searching for about 45 mins
Did not find any solutions…
if someone have found…
thx

2 
--liquibase formatted sql  
--changeset stevedonie:create-test-table endDelimiter:go
CREATE OR REPLACE FUNCTION week_to_date(
  IN in_year INTEGER,
  IN in_week INTEGER,
  IN in_dow INTEGER DEFAULT NULL
)
RETURNS DATE AS 
$$
BEGIN
  RETURN to_timestamp('1 ' || in_year, 'IW IYYY')::DATE + (COALESCE(in_dow, 1) + 6) % 7 + 7 * in_week - 7;
END;
$$ LANGUAGE plpgsql;
go
--rollback DROP FUNCTION
--rollback week_to_date
3

so , the developer is testing the creation procedure script using psql.
It is ok,
he have to modify the script to add delimiters ?
so the script executed with psql will not be the one executed with liquibase ?

it is not possible, i must have missed something…

4

If your are using SQL Changelog file then you can replace $ Sign with Single Quote and Single Quote with double Single Quote ‘’

Example : CREATE OR REPLACE FUNCTION public.testingfunctions()
RETURNS TABLE(“DistributorCode” character varying)
LANGUAGE plpgsql
AS ’
begin
RETURN QUERY
select * from testtable;
END;’