From oldiesmann@oldiesmann.us Fri Dec  11 08:09:13 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 64167632C60
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 04:09:13 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 30326-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 08:09:05 +0000 (UTC)
X-Greylist: delayed 01:47:04.287838 by SQLgrey-1.7.6
Received: from oldiesmann.fbyneserve.com (unknown [66.187.98.169])
	by mail.postgresql.org (Postfix) with ESMTP id C55C96315D5
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 04:09:05 -0400 (AST)
Received: from cpe-24-165-117-131.cinci.res.rr.com ([24.165.117.131]
	helo=[127.0.0.1])
	by oldiesmann.fbyneserve.com with esmtpa (Exim 4.69)
	(envelope-from <oldiesmann@oldiesmann.us>) id 1NIyt5-0006Le-4y
	for pgsql-sql@postgresql.org; Fri, 11 Dec 2009 01:21:59 -0500
Message-ID: <4B21E50A.7090404@oldiesmann.us>
Date: Fri, 11 Dec 2009 01:22:02 -0500
From: Michael Eshom <oldiesmann@oldiesmann.us>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.1.5) Gecko/20091130 Thunderbird/3.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: FIND_IN_SET
Content-Type: multipart/alternative;
	boundary="------------040708020903020602020707"
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - oldiesmann.fbyneserve.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12]
X-AntiAbuse: Sender Address Domain - oldiesmann.us
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.498 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001, RDNS_NONE=0.1
X-Spam-Level: 
X-Archive-Number: 200912/32
X-Sequence-Number: 33470

This is a multi-part message in MIME format.
--------------040708020903020602020707
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------040708020903020602020707
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#ffffff" text="#000000">
<font size="-1"><font face="Arial">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).<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,
regardless of how simple it is for the host to add it.<br>
<br>
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.<br>
<br>
Is there a way to do this without requiring plpgsql or
generate_subscripts?<br>
</font></font>
<div class="moz-signature">-- <br>
<font style="color: blue; size: 12px;" face="Comic Sans MS">Michael
"Oldiesmann" Eshom<br>
Christian Oldies Fan<br>
Cincinnati, Ohio</font></div>
</body>
</html>

--------------040708020903020602020707--



From pavel.stehule@gmail.com Fri Dec  11 08:48:46 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id D2BDF6321FB
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 04:48:46 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 83631-09 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri, 11 Dec 2009 08:48:32 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yx0-f181.google.com (mail-yx0-f181.google.com
	[209.85.210.181])
	by mail.postgresql.org (Postfix) with ESMTP id 08CCB6315D5
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 04:48:35 -0400 (AST)
Received: by yxe11 with SMTP id 11so586750yxe.15
	for <pgsql-sql@postgresql.org>; Fri, 11 Dec 2009 00:48:34 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=nbiXv/NCRDx+kHfm1hoS759A9S7Sx6p6YFUQzOdSWo4=;
	b=UVEug1vauonhXZYfiCJOn/QY1S3CymtDtaP09PSjSmzMFi1/zTgDVuBUf9dm3faqtL
	BZuOVy2VAikXyCKU2oio+RTPcUUISUEJeQjaVoCRCCEssSjRPvCYR2Vsr7Hq53H4GkAs
	21emfrcoW71iqgj4QF+Lk54EFrelDGAHmVfuQ=
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
Received: by 10.150.101.15 with SMTP id y15mr2051058ybb.162.1260521314601;
	Fri, 11 Dec 2009 00:48:34 -0800 (PST)
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
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.578 tagged_above=-10 required=5 tests=AWL=0.021,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200912/34
X-Sequence-Number: 33472

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