Thread: FIND_IN_SET

<font size="-1"><font face="Arial">I am on the marketing team for a popular forum system, and am also the primary
PostgreSQLtester/bugfixer. Currently our forum system treats MySQL's FIND_IN_SET() as a boolean (eg whether or not the
specifiedvalue was found in the given set), which is fine since MySQL will treat any integer greater than 0 as boolean
trueand 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
insteadof boolean).<br /><br /> 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,
regardlessof how simple it is for the host to add it.<br /><br /> I did find another version of FIND_IN_SET() on a blog
withseveral other MySQL-compatible functions, and while it uses regular SQL, it requires the generate_subscripts()
functionwhich isn't available in Postgres 8.1 - the latest version officially supported by CentOS.<br /><br /> Is there
away to do this without requiring plpgsql or generate_subscripts?<br /></font></font><div class="moz-signature">-- <br
/><fontface="Comic Sans MS" style="color: blue; size: 12px;">Michael "Oldiesmann" Eshom<br /> Christian Oldies Fan<br
/>Cincinnati, Ohio</font></div> 

2009/12/11 Michael Eshom <oldiesmann@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
SELECT0 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