I need to create trigger which does not depend on schema name but this does
not work when adding rows from pgadmin.
To reproduce:
1. Run the code
2. Open dok table in pgAdmin grid for editing.
3. Add new row. Enter g to doktyyp column. Try to save added row.
Observed result:
---------------------------
pgAdmin III
---------------------------
An error has occured:
ERROR: relation "dok_g_seq" does not exist
CONTEXT: PL/pgSQL function "dok_seq_trig" line 2 at assignment
Expected result:
No error.
Note.
If nexval() call is prefixed with schema name
NEW.dokumnr = nextval('demo.'||TG_RELNAME || '_'|| NEW.doktyyp ||'_seq');
error does not occur.
Is this PgAdmin bug ?
How to fix this without adding schema name to nextval() argument ?
Andrus.
Code to reproduce:
CREATE SCHEMA demo;
SET search_path TO demo,public;
CREATE TABLE dok ( doktyyp CHAR, dokumnr NUMERIC(7) );
CREATE SEQUENCE demo.dok_g_seq;
ALTER TABLE demo.dok_g_seq OWNER TO postgres;
CREATE OR REPLACE FUNCTION demo.dok_seq_trig() RETURNS "trigger" AS
$BODY$BEGIN
NEW.dokumnr = nextval(TG_RELNAME || '_'|| NEW.doktyyp ||'_seq');
RETURN NEW;
END$BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT;
CREATE TRIGGER dok_btrig BEFORE INSERT ON demo.dok FOR EACH ROW EXECUTE PROCEDURE demo.dok_seq_trig();
INSERT INTO dok (doktyyp) values ('g');
