CODEWITHSUNDEEP
postgresqlpostgresql-11
ggk
ggk

Reputation: 127

trigger function error in postgres, said that column doesn't exists when it actualy exists

I'm trying to implement full text search and have successfully created a trigger function

CREATE FUNCTION content_vector_generate() RETURNS trigger AS $$
begin
    new.content_vector := 
        to_tsvector('simple', coalesce("title" ,'') || ' ' || coalesce("content" ,''));
    return new;
    end
$$ LANGUAGE plpgsql;
     
CREATE TRIGGER content_vector_generate BEFORE INSERT OR UPDATE
        ON "Posts" FOR EACH ROW EXECUTE PROCEDURE content_vector_generate();

but when i try, to check the function work properly by inserting / updating row

INSERT INTO public."Posts"(id,title,content)
VALUES(1,'post-title','etc-content')

i got this error instead

SQL Error [42703]: ERROR: column "title" does not exist
  Where: PL/pgSQL function content_vector_generate() line 3 at assignment

do I have wrong syntax, i've tried removed the quotation mark too

Upvotes: 0

Views: 857

Answers (1)

ggk
ggk

Reputation: 127

as @a_horse_with_no_name mention i need to reference the column new row in the function like :

CREATE OR REPLACE FUNCTION content_vector_generate() RETURNS trigger AS $$
begin
    new.content_vector := 
        to_tsvector('simple', coalesce(new."title" ,'') || ' ' || coalesce(new."content" ,''));
    return new;
    end
$$ LANGUAGE plpgsql;

Upvotes: 1

Related Questions