Re: stored procedure - Mailing list pgsql-general

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
>