Re: stored procedure

i think i have worked out something acceptable to me on how the generate
a password, but since i am inexperienced at this, i would like other
people's opinion on how this would work. any comments will be appreciated.

--DROP FUNCTION passwdgen(TEXT);
DROP FUNCTION passwdgen();

--CREATE FUNCTION passwdgen(TEXT) RETURNS VARHCAR(6) AS '
-- FUNCTION for creating trigger

CREATE FUNCTION passwdgen() RETURNS OPAQUE AS '
DECLARE
-- initialize the passwd variable to concatenate
passwd VARCHAR(6) := '''';
index INT;
ok BOOL := false;
c charkey.c%TYPE;
r RECORD;
BEGIN
WHILE NOT ok LOOP -- passwd not exist in table
-- write separate function to check if passwd exists
FOR i IN 1..6 LOOP
-- get character and append to passwd
-- SELECT INTO index int4((3*random());
index := int4(3*random());
FOR r IN EXECUTE ''SELECT c FROM charkey WHERE idx='' ||
quote_literal(index) LOOP
passwd := passwd || r.c::VARCHAR(1);
END LOOP;
END LOOP;
FOR r IN EXECUTE ''SELECT
chckpasswd(''||quote_literal(passwd)||'',''||quote_literal(TG_RELNAME)||'')
AS b'' LOOP
ok := r.b;
END LOOP;
ok := true;
END LOOP;
NEW.password := passwd;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

DROP FUNCTION chckpasswd(VARCHAR(6), TEXT);

CREATE FUNCTION chckpasswd(VARCHAR(6), TEXT) RETURNS BOOL AS '
DECLARE
pass ALIAS FOR $1;
table ALIAS FOR $2;
ok BOOL := false;
r RECORD;
BEGIN
FOR r IN EXECUTE ''SELECT count(*) AS count FROM '' ||
quote_ident(table) || '' WHERE password='' || quote_literal(pass) LOOP
IF r.count=0 THEN
-- password is okay to use
ok := true;
ELSE
-- password is already in use
ok := false;
END IF;
END LOOP;
RETURN ok;
END;
' LANGUAGE 'plpgsql';

DROP TRIGGER passwdgen ON pass;

-- Create trigger for each table you want to generate a password
CREATE TRIGGER passwdgen BEFORE INSERT ON pass FOR EACH ROW EXECUTE
PROCEDURE passwdgen();
Peter Choe wrote:

> is there a listing of the built in procedure and what they do? i did
> 'select * from pg_proc' and got the names, but i don't know what they
> actually do.
>
> what i am looking for is some random character generator function. is
> there anything like that in postgres?
>
> Peter Choe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>