Message-ID: <00c801c2dde8$f69de7d0$0201a8c0@zimmer>
From: "Grignon Etienne" <egrignon@egrignon.com>
To: <pgsql-sql@postgresql.org>
Subject: pgsql problem
Date: Wed, 26 Feb 2003 23:46:54 +0100
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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.com
http://www.hans-zimmer.com