--
drop table constraint_table cascade;
create table constraint_table ( "id" bit(64) UNIQUE , set_values varchar UNIQUE);
insert into constraint_table values (B'0000000000000000000000000000000000000000000000000000000000000001','one');
insert into constraint_table values (B'0000000000000000000000000000000000000000000000000000000000000010','two');
insert into constraint_table values (B'0000000000000000000000000000000000000000000000000000000000000100','three');
insert into constraint_table values (B'0000000000000000000000000000000000000000000000000000000000001000','four');

drop function sx(varchar) ;
-- this function checks if the argument string
-- contains members which make a valid mysql set
-- returns whatever on success
-- returns the string '0' on failure
CREATE OR REPLACE FUNCTION sx( varchar ) RETURNS varchar AS $$
DECLARE

---

str_in ALIAS FOR $1; -- 'four','two','oranges'
arg_str VARCHAR ;
argx VARCHAR := '';
rec RECORD ;
curs CURSOR (key varchar) IS SELECT id, set_values FROM constraint_table where set_values = key;
nobreak INT := 1;
psn INT := 0;
bit_string BIT varying := '0000000000000000000000000000000000000000000000000000000000000000'; -- 64 long
member_count INT := 0;

---

BEGIN

---

IF str_in IS NULL THEN RETURN '0'; END IF;
arg_str := REGEXP_REPLACE(str_in, '\',\'', ','); -- str_in is CONSTANT
arg_str := REGEXP_REPLACE(arg_str, '\'', '');
arg_str := REGEXP_REPLACE(arg_str, '\'$', '');
RAISE NOTICE 'arg_str %',arg_str;
psn := POSITION(',' in arg_str);
IF psn > 0 THEN

        psn := psn - 1; -- minus-1 from comman position
        RAISE NOTICE 'psn %',psn;
        argx := SUBSTRING(arg_str FROM 1 FOR psn);  -- get one set member
        psn := psn + 2; -- go to first starting letter
        arg_str := SUBSTRING(arg_str FROM psn);   -- hack it off
ELSE
        psn := 0; -- minus-1 from comman position
        argx := arg_str;

END IF;
RAISE NOTICE 'argx %',argx;
RAISE NOTICE 'new arg_str: %',arg_str;
WHILE nobreak LOOP

        OPEN curs(argx);
        FETCH curs INTO rec;
        IF NOT FOUND THEN RETURN '0';
        ELSE 
                -- RAISE NOTICE 'bit_string \'%\' for \'%\'', rec.id, rec.set_values;
                bit_string := bit_string | rec.id  ;
                RAISE NOTICE 'bit_string \'%\' after \'%\'', bit_string, argx;
        END IF;
        CLOSE curs;
        IF psn > 0 THEN
                psn := psn - 1; -- minus-1 from comman position
                RAISE NOTICE 'psn %',psn;
                argx := SUBSTRING(arg_str FROM 1 FOR psn);  -- get one set member
                psn := psn + 2; -- go to first starting letter
                arg_str := SUBSTRING(arg_str FROM psn);   -- hack it off
                psn := POSITION(',' in arg_str);
        ELSE nobreak = 0; 
        END IF;
        RAISE NOTICE 'next argx % and next arg_str %', argx, arg_str;

END LOOP;
SELECT count(*) INTO member_count FROM constraint_table;
member_count := 64 - member_count;
bit_string := SUBSTRING(bit_string FROM member_count);
RETURN bit_string;

---

END;
$$ LANGUAGE plpgsql;
--
-- select sx('\'four\',\'two\'');
select sx('\'four\',\'two\'');
-- CREATE OR REPLACE FUNCTION sx( bytea ) RETURNS varchar AS $$
-- position(substring in string)
-- position(',' in 'Thomas')
-- length(string)
-- length('jose') returns 4
-- overlay(string placing string from int [for int])
-- overlay('Txxxxas' placing 'hom' from 2 for 4)
-- $$ LANGUAGE plpgsql;

---

Leave a Reply Your Name:     anonymous Your Email: Website:

Comments: The author will be notified of your reply.

return to top