Execute file with SQLExec containing $$ characters

Question

I've created a sql dump file using pg_dump. This export file contains functions which contain $$ characters. No problem to import the file with psql -f < filename>.

If want to import the file with ant using the SQLExec task, I get an exception like:

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 the SQLExec tasks converts $$ to $ which the causes the error.

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

Here 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);
    }
}

Can you include some sample data? `$$` is usually used for [dollar quoting](http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING) strings. — mu is too short, Jul 29 '12 at 20:27
CREATE FUNCTION time_to_sec(timepoint timestamp with time zone) RETURNS bigint LANGUAGE plpgsql AS $$ 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; $$; — markus, Jul 29 '12 at 20:32

score 2 · Accepted Answer · edited May 23 '17 at 11:48

$$ is just the bare minimum for dollar-quoting. Make it (much!) less likely to conflict with strings in the enclosed literal by putting a string between the 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$;

More advice

The assignment operator in plpgsql is :=. = is undocumented and may go away in future releases. More under this related question.
Use CURRENT_DATE instead of CURRENT_TIMESTAMP::date.
It is allowed, but I would advise not to use 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$;

Can be declared STABLE!

Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.

There is also the closely related function age() in PostgreSQL doing almost, but not quite, the same: it returns a "symbolic" result with standard-years and months. Therefore, expression with age() can yield different results for longer periods of time.

These are all equivalent - except for 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;

As to the original question: this PostgreSQL error message does not necessarily mean the problem is with the dollar sign:

ERROR: syntax error at or near "$"

Most of the time it's a missing ; before that line. Or maybe an un-escaped special characters in XML, like < > & ? The dollar sign $ should be fine. But I am no expert with ant. There should be more context in the PostgreSQL log.

Thanks for the explanations. In the logs it seems that $$ is converted to $ by the SQLExec task, that seems to be the root of the error. — markus, Jul 30 '12 at 06:22

Execute file with SQLExec containing $$ characters

1 Answers1

More advice

Linked