DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=ibnlsl2qUroQTPvKMrlE5eEVlVKyqBC+LTFsZrcyoxyQk1KEVACAtp2GJmASG1RYAz
	j5T6GzvNksrWqN5AObON/I0e7UtYx1QF9DvmyFow1G2hcuJWnJJdOpYVZUGU8kqJfnqH
	ITtBvPt00rOE+xNbYUUoDSBzbVBhmQWoV057A=
MIME-Version: 1.0
In-Reply-To: <4B21E50A.7090404@oldiesmann.us>
References: <4B21E50A.7090404@oldiesmann.us>
Date: Fri, 11 Dec 2009 09:48:33 +0100
Message-ID: <162867790912110048m26e26091ida65e1a9ae369690@mail.gmail.com>
Subject: Re: FIND_IN_SET
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Michael Eshom <oldiesmann@oldiesmann.us>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8

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
  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