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
