CODEWITHSUNDEEP
databasepostgresql
user5694199
user5694199

Reputation:

Postgresql ERROR:relation does not exist

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;

Upvotes: 1

Views: 6855

Answers (2)

Gary - Stand with Ukraine
Gary - Stand with Ukraine

Reputation: 7561

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

Upvotes: 1

astef
astef

Reputation: 9508

You should use a full name (with schema) from a function: SELECT nextval('users_test.res_id_mlb_seq')

Upvotes: 0

Related Questions