Postgresql ERROR:relation does not exist

Question

I have a problem with trigger/sequence postgreSQL

When I try to INSERT in my table res_letterbox I have this error :

ERROR: relation "res_id_mlb_seq" does not exist
LINE 1: SELECT nextval('res_id_mlb_seq') QUERY: SELECT nextval('res_id_mlb_seq') CONTEXT: PL/pgSQL function users_test.trigger_fct_t_res_letterbox_ins() line 5 at SQL statement

********** Error **********

ERROR: relation "res_id_mlb_seq" does not exist SQL state: 42P01
Context: PL/pgSQL function users_test.trigger_fct_t_res_letterbox_ins() line 5 at SQL statement

I tried to set directly in defaut value of the column res_id: nextval('res_id_mlb_seq'::regclass) and that it returns me:

ERROR:relation "res_id_mlb_seq" does not exist

--

CREATE SCHEMA users_test;
ALTER SCHEMA users_test OWNER TO users_test;

SET search_path = users_test;

 CREATE TABLE res_letterbox (
        res_id bigint,
    title varchar(255) DEFAULT 'NULL',
    ...
    ..
);

CREATE SEQUENCE res_id_mlb_seq INCREMENT 1 MINVALUE 1 NO MAXVALUE START 12602;

DROP TRIGGER IF EXISTS t_res_letterbox_ins ON res_letterbox CASCADE;
CREATE OR REPLACE FUNCTION trigger_fct_t_res_letterbox_ins() RETURNS trigger AS $BODY$
BEGIN
  BEGIN
      SELECT nextval('res_id_mlb_seq')
        INTO NEW.res_id;

EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
  END;
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql';

CREATE TRIGGER t_res_letterbox_ins
    BEFORE INSERT ON res_letterbox FOR EACH ROW
    EXECUTE PROCEDURE trigger_fct_t_res_letterbox_ins();  

ALTER SEQUENCE IF EXISTS res_id_mlb_seq RESTART WITH 12603;

BTW You can avoid the trigger+function by defining the column as bigserial. (+ maybe setval() ) — joop

Gary Gary · Accepted Answer · 2017-09-26T13:03:30

The function you're creating doesn't know that it is supposed to find the sequence in the users_test schema.

You can adjust this by specifying the search_path when you create the function:

CREATE OR REPLACE FUNCTION trigger_fct_t_res_letterbox_ins() RETURNS trigger AS $BODY$
BEGIN
  BEGIN
      SELECT nextval('res_id_mlb_seq')
        INTO NEW.res_id;

EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
  END;
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql'
 SET search_path = 'users_test';

Create function docs.

https://www.postgresql.org/docs/9.3/static/sql-createfunction.html

Postgresql ERROR:relation does not exist

2 Answers