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

Question

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

You need to reference the column in the NEW record: new.title and new.content. Additionally: string constants need to be enclosed in single quotes so your insert needs to be VALUES(1,'post-title','etc-content') — user330315
– user330315, Commented Feb 14, 2022 at 10:42

ggk · Accepted Answer · 2022-02-14 11:04:12Z

1

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;

answered Feb 14, 2022 at 11:04

ggk

1372 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related