plsql command works in tableplus but fails when done using goose migrations

[hackertron]

below is the sql code

-- +goose Up
-- Add full-text search capabilities
ALTER TABLE jobs ADD COLUMN search_vector tsvector;

-- Create GIN index for fast full-text search
CREATE INDEX jobs_search_idx ON jobs USING GIN(search_vector);

-- Create trigger function to update search_vector
CREATE OR REPLACE FUNCTION jobs_search_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  title_text text := COALESCE(NEW.title, '');
  description_text text := COALESCE(NEW.description, '');
  company_text text := COALESCE(NEW.company, '');
  location_text text := COALESCE(NEW.location, '');
BEGIN
  NEW.search_vector := (
    setweight(to_tsvector('english', title_text), 'A') ||
    setweight(to_tsvector('english', description_text), 'B') ||
    setweight(to_tsvector('english', company_text), 'C') ||
    setweight(to_tsvector('english', location_text), 'D')
  );
  RETURN NEW;
END;
$$;


-- Attach the trigger to the jobs table
CREATE TRIGGER jobs_search_update
  BEFORE INSERT OR UPDATE ON jobs
  FOR EACH ROW
  EXECUTE FUNCTION jobs_search_trigger();

-- +goose Down
-- Rollback: Remove full-text search functionality
DROP FUNCTION IF EXISTS jobs_search_trigger();
DROP TRIGGER IF EXISTS jobs_search_update ON jobs;
ALTER TABLE jobs DROP COLUMN IF EXISTS search_vector;
DROP INDEX IF EXISTS jobs_search_idx;

error

 ERROR 008_add_job_search.sql: failed to run SQL migration: failed to execute SQL query "CREATE OR REPLACE FUNCTION jobs_search_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$\nDECLARE\n  title_text text := COALESCE(NEW.title, '');": ERROR: unterminated dollar-quoted string at or near "$$
DECLARE
  title_text text := COALESCE(NEW.title, '');" (SQLSTATE 42601)

[mfridman]

You need to wrap those complex statements, this might help:

https://pressly.github.io/goose/documentation/annotations/#complex-statements

e.g.,

[...]

-- +goose statementbegin
CREATE OR REPLACE FUNCTION jobs_search_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
  title_text text := COALESCE(NEW.title, '');
  description_text text := COALESCE(NEW.description, '');
  company_text text := COALESCE(NEW.company, '');
  location_text text := COALESCE(NEW.location, '');
BEGIN
  NEW.search_vector := (
    setweight(to_tsvector('english', title_text), 'A') ||
    setweight(to_tsvector('english', description_text), 'B') ||
    setweight(to_tsvector('english', company_text), 'C') ||
    setweight(to_tsvector('english', location_text), 'D')
  );
  RETURN NEW;
END;
$$;
-- +goose statementend

[...]

[hackertron]

I can't thank you enough :) , wasted 2hrs on this 😭

[mfridman]

Sorry about that, maybe we can surface a better error here if we detect this

[hackertron]

Oh no, I should have checked the documentation first too. thanks. For the error maybe linking documentation might be a good idea too ? within the error for e.g. check goose documentation if sql is working but goose is failing .
thanks for the project though :D