Thread: stored procedure
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
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:
Peter Choe
Bruno Wolff III wrote:
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. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
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: > 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.) > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
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: > then do ( ( 6 * random() )::int ) % 6 > > 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.) >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> > >
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:
> 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
>