Execute a file using SQLExec containing $$ characters

Question

I created a sql dump file using pg_dump. This export file contains functions that contain the characters $$. There is no problem importing a file with psql -f <file_name>.

If you want to import a file using ant using the SQLExec task, I get an exception, for example:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$" 

Is there a way to import a file containing $$?

In the postgres log, it seems that SQLExec tasks convert $$ to $, which causes an error.

ERROR: syntax error at or near $$ in character 87 STATEMENT: CREATE FUNCTION process_create_servicenumber () RETURNS trigger LANGUAGE plpgsql AS $ BEGIN IF (TG_OP = 'DELETE') THEN RETURN OLD

Here is my method

 protected void importNewDbFromDumpFile() { final class SqlExecuter extends SQLExec { public SqlExecuter() { Project project = new Project(); project.init(); setProject(project); setTaskType("sql"); setTaskName("sql"); } } try { SqlExecuter executer = new SqlExecuter(); executer.setSrc(new File(dbDumpFileLocation)); executer.setClasspath(createClasspath()); executer.setEscapeProcessing(true); executer.setDriver("org.postgresql.Driver"); executer.setUrl("jdbc:postgresql://localhost/test"); executer.setPassword("test"); executer.setUserid("manager"); executer.execute(); } catch (Exception e) { log.info("Exception importing database ...", e); } } 

Answer 1

$$ is just the minimum for dollar quotes . Make this (a lot!) Less likely to conflict with strings in a closed literal by putting a string between dollars:

 CREATE OR REPLACE FUNCTION time_to_sec(timepoint timestamp with time zone) RETURNS bigint LANGUAGE plpgsql AS $BODY$ DECLARE seconds bigint; secondsFromEpoch bigint; secondsFromMidnight bigint; BEGIN secondsFromEpoch = EXTRACT(EPOCH FROM timepoint)::bigint; secondsFromMidnight = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP::date)::bigint; seconds = secondsFromEpoch - secondsFromMidnight; return seconds; END; $BODY$; 

Additional tips

• The assignment operator in plpgsql is := . = undocumented and may disappear in future releases. See more in this related question .

• Use CURRENT_DATE instead of CURRENT_TIMESTAMP::date .

• Allowed, but I would advise against using mixed case parameter names in plpgsql. They are case insensitive.

• Most importantly, simplify :

   CREATE OR REPLACE FUNCTION time_to_sec2(timepoint timestamp with time zone) RETURNS bigint LANGUAGE plpgsql STABLE AS $BODY$ BEGIN RETURN EXTRACT(EPOCH FROM timepoint - current_date)::bigint; END; $BODY$; 

  Or even:

   CREATE OR REPLACE FUNCTION time_to_sec3(timepoint timestamp with time zone) RETURNS bigint LANGUAGE sql AS $BODY$ SELECT EXTRACT(EPOCH FROM timepoint - current_date)::bigint; $BODY$; 

• You can declare STABLE !

Also note that the current_timestamp family of functions is consistent, since their values ​​do not change in a transaction.

• There is also a closely related age() function in PostgreSQL that is almost, but not quite, the same thing: it returns a “symbolic” result with standard years and months. Therefore, an expression with age() can give different results over longer periods of time.

All of them are equivalent - with the exception of the last two deviating with longer periods of time:

 WITH x(t) AS (VALUES ('2012-07-20 03:51:26+02'::timestamptz)) SELECT time_to_sec(t) AS t1 ,time_to_sec2(t) AS t2 ,time_to_sec3(t) AS t3 ,EXTRACT(EPOCH FROM t - current_date)::bigint AS t4 ,EXTRACT(EPOCH FROM age(t, current_date))::bigint AS t5 -- deviates ,EXTRACT(EPOCH FROM age(t))::bigint * -1 AS t6 -- deviates FROM x; 

---

Regarding the original question: this PostgreSQL error message does not necessarily mean that the problem is related to the dollar sign:

ERROR: syntax error at or near "$"

In most cases this is absent ; up to this line. Or perhaps unescaped special characters in XML, like < > & ? The dollar sign $ should be fine. But I am not an expert with ant. PostgreSQL should have more context.