2009/12/11 Michael Eshom <oldiesm...@oldiesmann.us>: > I am on the marketing team for a popular forum system, and am also the > primary PostgreSQL tester/bugfixer. Currently our forum system treats > MySQL's FIND_IN_SET() as a boolean (eg whether or not the specified value > was found in the given set), which is fine since MySQL will treat any > integer greater than 0 as boolean true and 0 as boolean false. I have > already managed to write a FIND_IN_SET() function for Postgres that behaves > as boolean. However, we would like to be able to use the true functionality > of this function (so it will return an integer instead of boolean). > > This is where I've run into a problem. The mysqlcompat package has a > FIND_IN_SET() in it, but it requires plpgsql, and I'd rather not require > something that a regular user can't install themselves, regardless of how > simple it is for the host to add it. > > I did find another version of FIND_IN_SET() on a blog with several other > MySQL-compatible functions, and while it uses regular SQL, it requires the > generate_subscripts() function which isn't available in Postgres 8.1 - the > latest version officially supported by CentOS. > > Is there a way to do this without requiring plpgsql or generate_subscripts?
Hello you can define own generate_subscripts function CREATE OR REPLACE FUNCTION find_in_set(str text, strlist text) RETURNS int AS $$ SELECT i FROM generate_series(string_to_array($2,','),1) g(i) WHERE (string_to_array($2, ','))[i] = $1 UNION ALL SELECT 0 LIMIT 1 $$ LANGUAGE sql STRICT; CREATE OR REPLACE generate_subscripts(anyarray, int) RETURNS SETOF int AS $$ SELECT generate_series(array_lower($1,$2), array_upper($1,$2)) $$ LANGUAGE sql; Regards Pavel Stehule > -- > Michael "Oldiesmann" Eshom > Christian Oldies Fan > Cincinnati, Ohio -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
