From: | "Grignon Etienne" <egrignon(at)egrignon(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | pgsql problem |
Date: | 2003-02-26 22:46:54 |
Message-ID: | 00c801c2dde8$f69de7d0$0201a8c0@zimmer |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)egrignon(dot)com
http://www.egrignon.com
http://www.hans-zimmer.com
From | Date | Subject | |
---|---|---|---|
Next Message | A.M. | 2003-02-27 01:14:44 | timestamp output as seconds since epoch? |
Previous Message | Tom Lane | 2003-02-26 21:09:14 | Re: Relation "pg_relcheck" |