CREATING USERS

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

CREATING USERS

От:
Aaron Spiteri <aaron@m1group.com.au>
Дата:
I am having some trouble with a PL/SQL script I have been trying to 
write.  The idea is that a table is created called user_table, This 
stores extra information about each user on the system.  There is a link 
in the user_table called usename which links up to pg_shadow.username.  I 
want to be able to create new users, alter existing users or drop users 
depending on insertion, deletion or altercation of the user_table.  The 
code I wrote looks like this:

CREATE FUNCTION insert_svoc_user() RETURNS OPAQUE AS '
DECLARE
	r record;
	b bool DEFAULT ''f'';
BEGIN
	FOR r IN SELECT pg_shadow.usename FROM pg_shadow WHERE pg_shadow.usename 
= NEW.usename LOOP
		IF r.usename = NEW.usename THEN
			b := ''t'';
		END IF;
	END LOOP;
	IF b = ''t'' THEN
		ALTER USER NEW.usename IN GROUP NEW.groname;
	ELSE
	 	CREATE USER NEW.usename IN GROUP NEW.groname; 
	END IF;
	RETURN NEW;
END;
'
LANGUAGE 'plpgsql';

/*
 * update_user
 * -----------
 * This functions changes a users group on update of the user_table table
 */
CREATE FUNCTION update_svoc_user() RETURNS OPAQUE AS '
DECLARE
	r record;
	b bool DEFAULT ''f'';
BEGIN
	FOR r IN SELECT usename FROM pg_shadow WHERE pg_shadow.usename = 
NEW.usename LOOP
		IF r.username = NEW.usename THEN
			b := ''t'';
		END IF;
	END LOOP;
	IF b = ''f'' THEN
	 	CREATE USER NEW.usename IN GROUP NEW.groname;		
	END IF
	IF NOT NEW.groname = OLD.groname AND b = ''t'' THEN
		ALTER USER NEW.usename IN GROUP NEW.groname;
	END IF;
	RETURN NEW;
	
END;
'
LANGUAGE 'plpgsql';


/* 
 * delete_user
 * This function removes a user from the database if they are deleted 
from
 * the user_det table.
 *
 */
CREATE FUNCTION delete_svoc_user() RETURNS OPAQUE AS '
DECLARE
	r record;
BEGIN
	FOR r IN SELECT usename FROM pg_shadow WHERE pg_shadow.usename = 
OLD.usename LOOP
		IF r.usename = OLD.usename THEN
			DROP USER OLD.usename;
		END IF;
	END LOOP;
	RETURN OLD;
END;
'
LANGUAGE 'plpgsql';


I am continuusly getting an error Can not copyObject() error writing to 
$1.  I have looked up the pg archives and think the problem may be a 
limitation with in the PL SQL code, thus I was thinking of trying to 
rewrite it in PL/ TCL can anyone give some pointers on how to do this.

Aaron


FAQ