From jim@luckydigital.com Tue Feb  11 22:26:51 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 56E23475843
	for <pgsql-sql@postgresql.org>; Tue, 11 Feb 2003 17:26:51 -0500 (EST)
Received: from luckydigital.com (www.luckydigital.com [203.109.157.4])
	by postgresql.org (Postfix) with ESMTP id 1F4D6474E5C
	for <pgsql-sql@postgresql.org>; Tue, 11 Feb 2003 17:26:50 -0500 (EST)
Received: from jax (203-109-198-1.akl.ihugultra.co.nz [203.109.198.1])
	by luckydigital.com (8.11.6/8.10.0) with SMTP id h1BMQoC25850
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 11:26:50 +1300
Message-ID: <00f201c2d21c$aa14c3c0$c900a8c0@jax>
From: "James Cooper" <jim@luckydigital.com>
To: "sql" <pgsql-sql@postgresql.org>
Subject: anal about my syntax
Date: Wed, 12 Feb 2003 11:26:47 +1300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00EF_01C2D289.A0DA2A80"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/223
X-Sequence-Number: 11621

This is a multi-part message in MIME format.

------=_NextPart_000_00EF_01C2D289.A0DA2A80
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello All=20

I was looking at this plpgsql function:=20

FOR rec IN EXECUTE
''select count(person_id) as total from person where person_email like ''''=
'' || $1 || ''%'''' and person_id IN=20
(
select cp.person_id from cluster_person cp, cluster c where cp.cluster_id =
=3D c.cluster_id and c.c_id =3D '' || $2 || ''group by cp.person_id
) ''
LOOP
tot =3D rec.total;
END LOOP;

It works fine - I was just wondering if you can:
1. execute this sql without a loop being used as its not required.
2. could this be a straight SQL function instead( I dont think you can appe=
nd strings together  in SQL functions )




------=_NextPart_000_00EF_01C2D289.A0DA2A80
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1126" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hello All </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I was looking at this plpgsql=20
function:&nbsp;<BR></FONT><FONT face=3DArial size=3D2><FONT=20
size=3D2></FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT size=3D2>FOR rec IN EXECUTE<BR>''sel=
ect=20
count(person_id) as total from person where person_email like '''''' || $1 =
||=20
''%'''' and person_id IN <BR>(<BR>select cp.person_id from cluster_person c=
p,=20
cluster c where cp.cluster_id =3D c.cluster_id and c.c_id =3D '' || $2 || '=
'group by=20
cp.person_id<BR>) ''<BR>LOOP<BR>tot =3D rec.total;<BR>END=20
LOOP;</FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT size=3D2></FONT></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><FONT size=3D2>It&nbsp;works fine - I was =
just=20
wondering if you can:</FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT size=3D2>1. execute this sql without=
 a loop=20
being used as its not required.</FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT size=3D2>2. could this be a straight=
 SQL=20
function instead( I dont think you can append strings together&nbsp; in SQL=
=20
functions )</FONT></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><FONT size=3D2></FONT></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><FONT size=3D2>&nbsp;</DIV></FONT><FONT co=
lor=3D#0000ff=20
size=3D1>
<P><FONT color=3D#000000 size=3D2></FONT><FONT color=3D#000000=20
size=3D2></FONT></FONT><FONT size=3D1>&nbsp;</P></FONT></FONT></BODY></HTML>

------=_NextPart_000_00EF_01C2D289.A0DA2A80--




From josh@agliodbs.com Wed Feb  12 01:10:26 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id F3B6E475BA1
	for <pgsql-sql@postgresql.org>; Tue, 11 Feb 2003 20:10:26 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 61A22475AFA
	for <pgsql-sql@postgresql.org>; Tue, 11 Feb 2003 20:10:26 -0500 (EST)
Received: from [216.135.165.74] (account <josh@agliodbs.com>)
	by davinci.ethosmedia.com (CommuniGate Pro WebUser 4.0.2)
	with HTTP id 2835260; Tue, 11 Feb 2003 17:10:35 -0800
From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: anal about my syntax
To: "James Cooper" <jim@luckydigital.com>,
	"sql" <pgsql-sql@postgresql.org>
X-Mailer: CommuniGate Pro Web Mailer v.4.0.2
Date: Tue, 11 Feb 2003 17:10:35 -0800
Message-ID: <web-2835260@davinci.ethosmedia.com>
In-Reply-To: <00f201c2d21c$aa14c3c0$c900a8c0@jax>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/229
X-Sequence-Number: 11627

James,

> FOR rec IN EXECUTE
> ''select count(person_id) as total from person where person_email
> like '''''' || $1 || ''%'''' and person_id IN=20
> (
> select cp.person_id from cluster_person cp, cluster c where
> cp.cluster_id =3D c.cluster_id and c.c_id =3D '' || $2 || ''group by
> cp.person_id
> ) ''
> LOOP
> tot =3D rec.total;
> END LOOP;
>=20
> It works fine - I was just wondering if you can:
> 1. execute this sql without a loop being used as its not required.

Not as written, no.

> 2. could this be a straight SQL function instead( I dont think you
> can append strings together  in SQL functions )

Yes, it could.   If I have time later, I'll draft you an example.

-Josh



From josh@agliodbs.com Thu Feb  13 00:59:41 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 74B0C4759BD
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 19:59:41 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id C5D254758C9
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 19:59:39 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2837295; Wed, 12 Feb 2003 16:59:48 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: "James Cooper" <jim@luckydigital.com>
Subject: Re: anal about my syntax
Date: Wed, 12 Feb 2003 16:59:26 -0800
User-Agent: KMail/1.4.3
References: <web-2835260@davinci.ethosmedia.com>
	<019301c2d236$325678f0$c900a8c0@jax>
In-Reply-To: <019301c2d236$325678f0$c900a8c0@jax>
Cc: "sql" <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Message-Id: <200302121659.26956.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/269
X-Sequence-Number: 11667

James,

A small efficiency improvement, and a syntax fix:

CREATE FUNCTION get_people_letter_total( text, integer )  RETURNS integer
AS '
   select count(person_id) as total from person where person_email like
 ($1 || ''%'') and EXISTS
 (
 select cp.person_id from cluster_person cp, cluster c where cp.cluster_id =
=3D
 c.cluster_id and c.c_id =3D $2 and cp.person_id =3D person.person_id);
 ' LANGUAGE SQL;

 > I tried the above but my result is always 0;

Yes,  that's because of your syntax mistake in your "like" expression.

One more comment:  the above function will only work case-sensitively; i.e.=
,=20
it won't return a count for SMITHERS@yahoo.com is you search on "smithers".=
=20=20
You may want to consider putting a functional index on=20
lower(person.person_email), and changing the above to:

CREATE FUNCTION get_people_letter_total( text, integer )  RETURNS integer
AS '
   select count(person_id) as total from person where lower(person_email) l=
ike=20
lower($1 || ''%'') and EXISTS
 (
 select cp.person_id from cluster_person cp, cluster c where cp.cluster_id =
=3D
 c.cluster_id and c.c_id =3D $2 and cp.person_id =3D person.person_id);
 ' LANGUAGE SQL;


--=20
Josh Berkus
Aglio Database Solutions
San Francisco