From: Grignon Etienne <egrignon@egrignon.com>
To: pgsql-sql@postgresql.org
Subject: pgsql problem
Date: Wed, 26 Feb 2003 23:46:54 +0100
Message-ID: <00c801c2dde8$f69de7d0$0201a8c0@zimmer> (raw)
Hello,
I use postgresql 7.3.2
This is the code to show my problem :
-3 tables
-1 trigger
-2 stocked procedures
-1 view
-----------
DROP TABLE USERS CASCADE;
CREATE TABLE USERS (
U_ID SERIAL not null,
NAME VARCHAR(30) not null,
constraint PK_USERS primary key (u_id));
DROP TABLE PERMISSIONS CASCADE;
CREATE TABLE PERMISSIONS (
P_ID SERIAL not null,
NAME VARCHAR(30) not null,
constraint PK_PERM primary key (p_id));
DROP TABLE USER_PERM;
CREATE TABLE USER_PERM (
U_ID INT4 not null,
P_ID INT4 not null,
STATE BOOL not null default true,
constraint PK_USER_PERM primary key (U_ID, P_ID),
constraint FK_USER_PERM_USER foreign key (U_ID)
references USERS (U_ID)
on delete cascade on update restrict,
constraint FK_USER_PERM_PERM foreign key (P_ID)
references PERMISSIONS (P_ID)
on delete cascade on update restrict);
INSERT INTO permissions(name) VALUES('delete');
INSERT INTO permissions(name) VALUES('update');
INSERT INTO permissions(name) VALUES('create');
DROP TRIGGER OnCreateUser ON users;
DROP FUNCTION CreateUserTrig();
CREATE FUNCTION CreateUserTrig () RETURNS TRIGGER AS '
DECLARE
BEGIN
RAISE NOTICE ''TRIGGER'';
INSERT INTO user_perm(u_id, p_id)
(SELECT u_id, p_id FROM users, permissions
WHERE u_id = NEW.u_id);
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER OnCreateUser
AFTER INSERT
ON users FOR EACH ROW
EXECUTE PROCEDURE CreateUserTrig();
CREATE OR REPLACE FUNCTION CreateUser(text, bool, bool, bool) RETURNS int AS
'
DECLARE
Vname ALIAS FOR $1;
Vcreate_state ALIAS FOR $2;
Vupdate_state ALIAS FOR $3;
Vdelete_state ALIAS FOR $4;
tmp record;
BEGIN
INSERT INTO users(name) VALUES(Vname);
RAISE NOTICE ''Begin Of Update Permissions'';
UPDATE user_perm SET
state = Vcreate_state
WHERE u_id = currval(''users_u_id_seq'')
AND p_id = (SELECT p_id FROM permissions WHERE name = ''create'');
UPDATE user_perm SET
state = Vupdate_state
WHERE u_id = currval(''users_u_id_seq'')
AND p_id = (SELECT p_id FROM permissions WHERE name = ''update'');
UPDATE user_perm SET
state = Vdelete_state
WHERE u_id = currval(''users_u_id_seq'')
AND p_id = (SELECT p_id FROM permissions WHERE name = ''delete'');
RAISE NOTICE ''End Of Update Permissions'';
RETURN 0;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE VIEW ShowUserPerms AS
SELECT
U.u_id,
U.name,
P.p_id,
P.name AS "permission",
UP.state
FROM users AS U, user_perm AS UP, permissions AS P
WHERE UP.u_id = U.u_id AND P.p_id = UP.p_id ORDER BY name;
------------
test=# SELECT createuser('toto', true, true, true);
NOTICE: Begin Of Update Permissions
NOTICE: End Of Update Permissions
NOTICE: TRIGGER
createuser
------------
0
(1 row)
test=#
My problem is that the trigger for the insert is executed at the end of the
procedure, so I can't do the update of the user's permissions.
For me and my co-workers, the NOTICE TRIGGER should be before Begin Of
Update Permissions.
Could you explain to me how is it working ?
--
GRIGNON Etienne
Epitech Promo 2005
egrignon@egrignon.com
http://www.egrignon.comhttp://www.hans-zimmer.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: egrignon@egrignon.com
Subject: Re: pgsql problem
In-Reply-To: <00c801c2dde8$f69de7d0$0201a8c0@zimmer>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox