unterminated dollar-quoted string at or near "$$"

[daddycarbone]

I am trying to create a function in .sql for migration with sql-migrate but I got the error

pq: unterminated dollar-quoted string at or near "$$
BEGIN
    NEW.updated_at = NOW()"

he is my sytanx in .sql file

-- +migrate Up
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

should I manually create on the database? and cant allow on pq? I was changed to $BODY$ as well and still has the same error

[anfimovoleh]

I have the same issue. In some cases, you can do an inline definition for your function inside the SQL file, and it works. But the problem still exists, and behavior is not transparent.

[cbandy]

This is not caused by lib/pq. Something is mangling the SQL where it sees a ; and sending that to the driver. The error comes from PostgreSQL:

$ psql -U postgres -h localhost -f - <<'EOF'
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW() 
EOF            

psql:<stdin>:4: ERROR:  unterminated dollar-quoted string at or near "$$
BEGIN
    NEW.updated_at = NOW()"
LINE 2:     RETURNS TRIGGER AS $$

With the full statement, success:

$ psql -U postgres -h localhost -f - <<'EOF'
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;
EOF

CREATE FUNCTION

[anfimovoleh]

@daddycarbone @cbandy

Here's the solution for github.com/rubenv/sql-migrate

-- +migrate Up
CREATE TABLE people (id int);

-- +migrate StatementBegin
CREATE OR REPLACE FUNCTION do_something()
returns void AS $$
DECLARE
  create_query text;
BEGIN
  -- Do something here
END;
$$
language plpgsql;
-- +migrate StatementEnd

-- +migrate Down
DROP FUNCTION do_something();
DROP TABLE people;

[arielitovsky]

@daddycarbone @cbandy

Here's the solution for github.com/rubenv/sql-migrate

-- +migrate Up
CREATE TABLE people (id int);

-- +migrate StatementBegin
CREATE OR REPLACE FUNCTION do_something()
returns void AS $$
DECLARE
  create_query text;
BEGIN
  -- Do something here
END;
$$
language plpgsql;
-- +migrate StatementEnd

-- +migrate Down
DROP FUNCTION do_something();
DROP TABLE people;

I Think it's worth highlight the important part is to add: -- +migrate StatementBegin

and

-- +migrate StatementEnd

That was a good waste of an hour