PostgreSQL declaring and use serial variables inside trigger function

Question

CREATE OR REPLACE FUNCTION function_create_forum()
  RETURNS trigger AS
  $$
  BEGIN
    DECLARE  id_course   serial; 

    select id INTO id_course   FROM INSERTED;

    insert INTO Forum (course,name,type,staff_only) VALUES 
                      (id_course,"Staff lounge",1,true);
    --forum 2 creation
    --forum 3 creation

  END;
  $$
  LANGUAGE plpgsql VOLATILE;

The trigger function should create a new forum as long a course is created, the course has a "id" field wich is serial, and that is causing problems because is not accepted inside "DECLARE" area.

Since in comments anyone continue pointing the syntax above is incorrect (regardless that is working fine in all other triggers that don't use "serial") here is another variant of the code that don't work.

CREATE OR REPLACE FUNCTION function_create_forum()
  RETURNS trigger AS
  $$
  DECLARE
    id_course int;
  BEGIN

    select id INTO id_course   FROM INSERTED; 

    insert INTO Forum (course,name,type,staff_only) VALUES 
                      (id_course,"Staff lounge",1,true);
    --forum 2 creation
    --forum 3 creation

  END;
  $$
  LANGUAGE plpgsql VOLATILE;

table creation:

CREATE TABLE Forum (
    course serial REFERENCES Course(id) ON DELETE CASCADE NOT NULL,
    --omitted details
);

CREATE TABLE Course (
    id   serial  UNIQUE NOT NULL, --the primary key is another column
    --omitted details
)

serial is not a distinct data type, it's just a shorthand for declaring an int type, with a default clause which has a nextval() of a sequence (but only in table context) postgresql.org/docs/current/static/… -- in other contexts, just use int. — pozs
– pozs, Commented Feb 16, 2015 at 10:12
why? error message? more details? This isn't working too, but this won't work after all. -- i see now, your syntax is invalid: you can have only one DECLARE block, before the BEGIN block postgresql.org/docs/current/static/plpgsql-declarations.html — pozs
– pozs, Commented Feb 16, 2015 at 10:15
still, you can't use serial (that's not a real type), use int -- declare cannot be within the begin-end block, unless, it has a separate, inner begin-end block too (see the examples at the links i provided) — pozs
– pozs, Commented Feb 16, 2015 at 10:37
"regardless that is working fine in all other triggers" - I highly doubt that. There is no inserted in Postgres — user330315
– user330315, Commented Feb 16, 2015 at 10:59

score 7 · Accepted Answer · 2015-02-16 13:42:00Z

7

As others have already commented, there is no "data type" serial.

Quote from the manual:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases)

^{Emphasis mine}

One fundamental problem I can see is this line:

select id INTO id_course   FROM INSERTED;

There is no "inserted" virtual table in Postgres (like e.g. in SQL Server) If you create a row level trigger, then you can just access the values of the new row using the NEW variable which is implicitly defined in a trigger function.

Another problem is "Staff lounge". Double quotes are used to denote column (or table) names, not character literals. Character literals are enclosed in single quotes in SQL.

So your trigger function should look something like this:

CREATE OR REPLACE FUNCTION function_create_forum()
  RETURNS trigger AS
  $$
  BEGIN

    insert INTO Forum (course,name,type,staff_only) VALUES 
                      (new.id,'Staff lounge',1,true);
    --forum 2 creation
    --forum 3 creation

  END;
  $$
  LANGUAGE plpgsql VOLATILE;

You didn't show us your create trigger statement, but it should be something like this, to make sure you are creating a row level trigger:

CREATE TRIGGER course_insert_trg 
   AFTER INSERT ON course  --<< AFTER is important!
   FOR EACH ROW ---<<< this makes it a row level trigger
   EXECUTE PROCEDURE function_create_forum();

edited Feb 16, 2015 at 13:42

answered Feb 16, 2015 at 10:56

user330315

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

2 Comments

CoffeDeveloper Over a year ago

that don-t work because "new.id" refers to row that not exists (hence violates REFERENCES of Forum)

user330315 Over a year ago

@DarioOO: ah, right. You need to change that to an after insert trigger.

Collectives™ on Stack Overflow

PostgreSQL declaring and use serial variables inside trigger function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related