CREATING USERS - Mailing list pgsql-admin

From Aaron Spiteri
Subject CREATING USERS
Date
Msg-id 20020424.425300@scooby.m1group.com.au
Whole thread Raw
List pgsql-admin
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



pgsql-admin by date:

Previous
From: Bradley Kieser
Date:
Subject: JDBC driver and JBuilder 4 on Linux Anyone had success?
Next
From: Aaron Spiteri
Date:
Subject: upgrade on debian