stored procedure

On Tue, Apr 01, 2003 at 09:36:28 -0500,
Peter Choe <choepete@mindspring.com> 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?

There is a random function which you could use to get character data
(using a table lookup if nothing else). I wouldn't use that random
number generator for anything security related where the cost of
failure is high. It also might not be suitable for some kinds of
data analysis.

thanks. i just tried it out and it seems that it would generate a
random number between 0 and 1. is that a valid assumption? if that is
the case, how can i cast it to an int value? i assume that there is a
cast function somewhere, but i can't tell by the names of the pronames.

Peter Choe

Bruno Wolff III wrote:

On Tue, Apr 01, 2003 at 11:26:47 -0500,
Peter Choe <choepete@mindspring.com> wrote:

thanks. i just tried it out and it seems that it would generate a
random number between 0 and 1. is that a valid assumption? if that is
the case, how can i cast it to an int value? i assume that there is a
cast function somewhere, but i can't tell by the names of the pronames.

Normally you multiply by the range you want and then you can make a cast.
Note however, that multiplication may cause a value to round up so that
(6*random())::int might on very rare occasions return 6 instead of the
expected 0, 1, 2, 3, 4 or 5. (At least on some systems.)

then do ( ( 6 * random() )::int ) % 6

Bruno Wolff III wrote:

i tested the random thing and it seems to do something strange. i have
the following table set up:
| idx | c |
|-----|--- |
| 0 | o |
| 1 | a |
| 2 | b |
| 3 | c |
----------

i do the following:
select c from charkey where idx=((4 * random())::int)%4;

sometimes i get one character back (which is good), but other times, i
get two characters or none.

by the way, i am using postgres 7.2 on freebsd 4.8.

Peter Choe

Dennis Gearon wrote:

Peter Choe <choepete@mindspring.com> writes:

i do the following:
select c from charkey where idx=((4 * random())::int)%4;
sometimes i get one character back (which is good), but other times, i
get two characters or none.

The WHERE clause is re-evaluated at each row, so you get a new random
value each time. You need to compute *one* random value and then pull
the matching row out of the table.

regards, tom lane

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: