I'm trying to create tigger function but I cannot ...
# orm-helpt
Tarik Brat
08/26/2021, 12:46 PMI'm trying to create tigger function but I cannot use $$ delimeter, then I tried with ' and it worked but I'm receiving this error: `The column
newr
Ryan
08/26/2021, 12:49 PMAre you creating it in Migrate?
đź‘€ 1
t
Tarik Brat
08/26/2021, 12:53 PMIm creating it in my sql schema then I'm using introspect and generate
Tarik Brat
08/26/2021, 12:55 PMCreating table works and everything else but triggers not, I mean they get created but then there's that strange error.
r
Ryan
08/26/2021, 12:56 PMTriggers will not be shown in your schema. If you’re creating them in SQL directly then it should work fine unless there’s a syntax error in the trigger.
t
Tarik Brat
08/26/2021, 12:57 PM Copy code
CREATE TABLE members (
name varchar(50) NOT NULL,
username varchar(50)
);
CREATE OR REPLACE FUNCTION insert_username()
RETURNS trigger
LANGUAGE plpgsql AS 'BEGIN IF NEW.username IS NULL THEN NEW.username := to_tsvector(NEW.name); END IF; RETURN NEW; END;';
CREATE trigger trig_insert_members
BEFORE INSERT ON members
FOR EACH ROW
EXECUTE PROCEDURE insert_username();
insert into members(name) values('asd');Tarik Brat
08/26/2021, 12:58 PMTarik Brat
08/26/2021, 12:58 PMWorks on db-fiddle
Tarik Brat
08/26/2021, 1:00 PMImportant: I get this error when trying to insert new row.
r
Ryan
08/26/2021, 1:02 PMSo the above SQL doesn’t work on your database?
t
Tarik Brat
08/26/2021, 1:02 PMIt doesn't.
r
Ryan
08/26/2021, 1:33 PMTried this and it works perfectly:
Copy code
CREATE TABLE "members" (
"name" varchar(50) NOT NULL,
"username" varchar(50)
);
CREATE OR REPLACE FUNCTION insert_username()
RETURNS trigger
language plpgsql as $$
begin
if NEW."username" is null then
NEW."username" := 'something';
end if;
return NEW;
end;
$$;
CREATE trigger trig_insert_members
BEFORE INSERT ON "members"
FOR EACH ROW
EXECUTE PROCEDURE insert_username();
insert into "members" ("name") values('asd');
select * from "members";to_tsvector4 Views