Обсуждение: Adding row from pgAdmin does not work in private schema

Поиск
Список
Период
Сортировка

Adding row from pgAdmin does not work in private schema

От
"Andrus Moor"
Дата:
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'); 




Re: Adding row from pgAdmin does not work in private

От
Andreas Pflug
Дата:
Andrus Moor wrote:
> ---------------------------
> 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 ?

Obviously not. See pgsql docs for nextval, further support in 
pgsql-novice or pgsql-sql.

Regards,
Andreas