pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

FIND_IN_SET
2+ messages / 2 participants
[nested] [flat]

* FIND_IN_SET
@ 2009-12-11 06:22  Michael Eshom <oldiesmann@oldiesmann.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Michael Eshom @ 2009-12-11 06:22 UTC (permalink / raw)
  To: pgsql-sql

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?
-- 
Michael "Oldiesmann" Eshom
Christian Oldies Fan
Cincinnati, Ohio

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: FIND_IN_SET
@ 2009-12-11 08:48  Pavel Stehule <pavel.stehule@gmail.com>
  parent: Michael Eshom <oldiesmann@oldiesmann.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Pavel Stehule @ 2009-12-11 08:48 UTC (permalink / raw)
  To: Michael Eshom <oldiesmann@oldiesmann.us>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2009-12-11 08:48 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-11 06:22 FIND_IN_SET Michael Eshom <oldiesmann@oldiesmann.us>
2009-12-11 08:48 ` Pavel Stehule <pavel.stehule@gmail.com>

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