From: Pavel Stehule <pavel.stehule@gmail.com>
To: Michael Eshom <oldiesmann@oldiesmann.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: FIND_IN_SET
Date: Fri, 11 Dec 2009 09:48:33 +0100
Message-ID: <162867790912110048m26e26091ida65e1a9ae369690@mail.gmail.com> (raw)
In-Reply-To: <4B21E50A.7090404@oldiesmann.us>
References: <4B21E50A.7090404@oldiesmann.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, oldiesmann@oldiesmann.us
Subject: Re: FIND_IN_SET
In-Reply-To: <162867790912110048m26e26091ida65e1a9ae369690@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox