From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Michael Eshom <oldiesmann(at)oldiesmann(dot)us> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: FIND_IN_SET |
Date: | 2009-12-11 08:48:33 |
Message-ID: | 162867790912110048m26e26091ida65e1a9ae369690@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
2009/12/11 Michael Eshom <oldiesmann(at)oldiesmann(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas | 2009-12-11 10:20:47 | Re: Re: constants in 2-column foreign keys or how to design a storage for text-groups ? |
Previous Message | Jasen Betts | 2009-12-11 08:39:47 | Re: constants in 2-column foreign keys or how to design a storage for text-groups ? |