I am using Sphinx to index my database.
The problem is I have to filter the result by a character varying field.
So I have to find a way to convert character varying to sql_attr_uint.
I know that CRC32 in mysql can do the trick. Is there a CRC32 or any replacement in PostgreSQL?
-
Possible duplicate: stackoverflow.com/questions/5880300/…DrColossos– DrColossos2011-07-23 13:56:25 +00:00Commented Jul 23, 2011 at 13:56
-
@DrColossos Nice try but I need to return a number not a string.Shisoft– Shisoft2011-07-23 14:34:05 +00:00Commented Jul 23, 2011 at 14:34
Add a comment
|
2 Answers
This is the CRC32 function that defines thinking sphinx (gem):
CREATE OR REPLACE FUNCTION crc32(word text)
RETURNS bigint AS $$
DECLARE tmp bigint;
DECLARE i int;
DECLARE j int;
DECLARE byte_length int;
DECLARE word_array bytea;
BEGIN
IF COALESCE(word, '') = '' THEN
return 0;
END IF;
i = 0;
tmp = 4294967295;
byte_length = bit_length(word) / 8;
word_array = decode(replace(word, E'\\\\', E'\\\\\\\\'), 'escape');
LOOP
tmp = (tmp # get_byte(word_array, i))::bigint;
i = i + 1;
j = 0;
LOOP
tmp = ((tmp >> 1) # (3988292384 * (tmp & 1)))::bigint;
j = j + 1;
IF j >= 8 THEN
EXIT;
END IF;
END LOOP;
IF i >= byte_length THEN
EXIT;
END IF;
END LOOP;
return (tmp # 4294967295);
END
$$ IMMUTABLE LANGUAGE plpgsql;
Comments
Maybe you can use decode(substring(md5('foo') for 8), 'hex'). This would get you bytea of first 4 bytes of md5 hash of this string.
You can convert it to integer using something like:
create function bytea_to_integer(bytea)
returns integer strict
language sql as $$
select
(get_byte($1,0)*1::integer<<0*8)
+(get_byte($1,1)*1::integer<<1*8)
+(get_byte($1,2)*1::integer<<2*8)
+(get_byte($1,3)*1::integer<<3*8);
$$;
3 Comments
Shisoft
Thanks a lot,but where to put those code in Sphinx configure file?
Tometzky
I don't know much about Sphinx, but as I understand you'd need to add additional integer column to your table, which can be automatically filled using a trigger, and configure Sphinx to use this column as attribute.
tmg_tt
you could put function creation at sql_qury_pre and make Sql_query = select *, byte_to_int ( attr1 ) as attr2 from idx; sql_attr_uint = attr2