From yudie@axiontech.com Thu Dec  16 23:55:52 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 3D5FD3CB64F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Dec 2004 23:55:52 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 58554-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 16 Dec 2004 23:55:45 +0000 (GMT)
Received: from axiontech.com (unknown [216.52.172.78])
	by svr1.postgresql.org (Postfix) with SMTP id 7D8F73CB61F
	for <pgsql-sql@postgresql.org>; Thu, 16 Dec 2004 23:55:46 +0000 (GMT)
Received: (qmail 14522 invoked from network); 16 Dec 2004 23:55:58 -0000
Received: from pc-00161 (HELO yudie) (192.168.1.161)
	by mail.axiontech.com (192.168.1.6) with SMTP;
	16 Dec 2004 23:55:58 -0000
Message-ID: <001901c4e3ca$7c71aca0$a101a8c0@home.axiontech.com>
From: "Yudie" <yudie@axiontech.com>
To: <pgsql-sql@postgresql.org>
Subject: plpgsql.. SELECT INTO ... WHERE FIELD LIKE
Date: Thu, 16 Dec 2004 17:53:43 -0600
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0016_01C4E398.2F19CA50"
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 amavisd-new at hub.org
X-Spam-Status: No, hits=0.465 tagged_above=0 required=5 tests=AWL,
	HTML_MESSAGE, UPPERCASE_50_75
X-Spam-Level: 
X-Archive-Number: 200412/108
X-Sequence-Number: 19728

This is a multi-part message in MIME format.

------=_NextPart_000_0016_01C4E398.2F19CA50
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

How in plpgsql use LIKE with a variable?

let say I want to do this query:

     SELECT INTO RS id FROM customer WHERE firstname LIKE keyword% LIMIT =
1;

keyword is a variable, in this case I want to find name like 'Jo%'


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
Full function:

CREATE OR REPLACE FUNCTION custlike(text) RETURNS INT4 AS'

  DECLARE
    keyword ALIAS FOR $1;
    RS RECORD;

  BEGIN
    SELECT INTO RS id FROM customer WHERE firstname like keyword% LIMIT =
1;
    IF FOUND THEN
      RETURN RS.id;
    ELSE
       RETURN NULL;
    END IF;

  =20
 END'
LANGUAGE 'PLPGSQL';

------=_NextPart_000_0016_01C4E398.2F19CA50
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.1106" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>How in plpgsql&nbsp;use LIKE with a=20
variable?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>let say I want to do this =
query:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;&nbsp; SELECT=20
INTO&nbsp;RS&nbsp;id FROM customer WHERE firstname LIKE keyword% LIMIT=20
1;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>keyword is a variable, in this case I =
want to find=20
name like 'Jo%'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial=20
size=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Full function:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>CREATE OR REPLACE FUNCTION =
custlike(text) RETURNS=20
INT4 AS'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp; DECLARE</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp; &nbsp;keyword ALIAS FOR=20
$1;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp; &nbsp; RS RECORD;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp; BEGIN</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; SELECT =
INTO&nbsp;RS&nbsp;id FROM=20
customer WHERE firstname like keyword% LIMIT 1;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; IF FOUND =
THEN</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN=20
RS.id;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; ELSE</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
RETURN=20
NULL;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp;&nbsp; END IF;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;&nbsp; </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp;END'</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>LANGUAGE 'PLPGSQL';</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV></BODY></HTML>

------=_NextPart_000_0016_01C4E398.2F19CA50--





From mike@fuhr.org Fri Dec  17 00:46:42 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9AF193B6675
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 00:46:42 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 75696-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 00:46:24 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id F25DC3CB6EE
	for <pgsql-sql@postgresql.org>; Fri, 17 Dec 2004 00:46:20 +0000 (GMT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id iBH0kHvC013258
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 16 Dec 2004 17:46:20 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id iBH0kHTO009147;
	Thu, 16 Dec 2004 17:46:17 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.1/8.13.1/Submit) id iBH0kHnU009146;
	Thu, 16 Dec 2004 17:46:17 -0700 (MST) (envelope-from mfuhr)
Date: Thu, 16 Dec 2004 17:46:17 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Yudie <yudie@axiontech.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: plpgsql.. SELECT INTO ... WHERE FIELD LIKE
Message-ID: <20041217004617.GA9115@winnie.fuhr.org>
References: <001901c4e3ca$7c71aca0$a101a8c0@home.axiontech.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <001901c4e3ca$7c71aca0$a101a8c0@home.axiontech.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.106 tagged_above=0 required=5 tests=AWL,
	UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200412/109
X-Sequence-Number: 19729

On Thu, Dec 16, 2004 at 05:53:43PM -0600, Yudie wrote:

> How in plpgsql use LIKE with a variable?
> 
> let say I want to do this query:
> 
>      SELECT INTO RS id FROM customer WHERE firstname LIKE keyword% LIMIT 1;
> 
> keyword is a variable, in this case I want to find name like 'Jo%'

Use the concatenation operator (||):

SELECT ... WHERE firstname LIKE keyword || ''%'' LIMIT 1;

Notice the two single quotes, which are necessary if the function body
is surrounded by quotes.  Life gets easier in 8.0 with dollar quoting.

Are you planning to add more code to your PL/pgSQL function?  If not,
then you could replace it with a simple SQL function:

CREATE OR REPLACE FUNCTION custlike(TEXT) RETURNS INTEGER AS '
SELECT id FROM customer WHERE firstname LIKE $1 || ''%'' LIMIT 1;
' LANGUAGE sql;

The function will return NULL if it finds no records.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



From cbbrowne@acm.org Fri Dec  17 03:50:17 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id ED48F3CB03D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 03:50:17 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 37727-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 03:50:00 +0000 (GMT)
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2])
	by svr1.postgresql.org (Postfix) with ESMTP id D75613CBA9F
	for <pgsql-sql@postgresql.org>; Fri, 17 Dec 2004 03:50:03 +0000 (GMT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id 8CE0C31959; Fri, 17 Dec 2004 04:50:03 +0100 (MET)
From: Christopher Browne <cbbrowne@acm.org>
X-Newsgroups: pgsql.sql
Subject: Re: plpgsql.. SELECT INTO ... WHERE FIELD LIKE
Date: Thu, 16 Dec 2004 22:08:13 -0500
Organization: cbbrowne Computing Inc
Lines: 36
Message-ID: <m3vfb139lu.fsf@knuth.knuth.cbbrowne.com>
References: <001901c4e3ca$7c71aca0$a101a8c0@home.axiontech.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@news.hub.org
X-message-flag: Outlook is rather hackable, isn't it?
X-Home-Page: http://www.cbbrowne.com/info/
X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4 (Security Through
 Obscurity,
	linux)
Cancel-Lock: sha1:7eG3WW9ToM8lTi8QdJbTR6PH8aY=
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.437 tagged_above=0 required=5 tests=AWL, INFO_TLD,
	UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200412/111
X-Sequence-Number: 19731

Oops! yudie@axiontech.com ("Yudie") was seen spray-painting on a wall:
> How in plpgsql use LIKE with a variable?
>
> let say I want to do this query:
>      SELECT INTO RS id FROM customer WHERE firstname LIKE keyword% LIMIT 1;
>
> keyword is a variable, in this case I want to find name like 'Jo%'
>
> CREATE OR REPLACE FUNCTION custlike(text) RETURNS INT4 AS'
>   DECLARE
>     keyword ALIAS FOR $1;
>     RS RECORD;
>   BEGIN
>     SELECT INTO RS id FROM customer WHERE firstname like keyword% LIMIT 1;
>     IF FOUND THEN
>       RETURN RS.id;
>     ELSE
>        RETURN NULL;
>     END IF;
>  END'
> LANGUAGE 'PLPGSQL';

Try:
   SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || ''%'' LIMIT 1;

You append KEYWORD and a '%' together using ||.  You need to use
doubled quotes inside the quoted environment; one gets stripped off so
that the stored procedure will wind up containing the query:

   SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || '%' LIMIT 1;
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/sgml.html
C is almost a real language. (see assembler) Even the name sounds like
it's gone through  an optimizing  compiler.  Get  rid of  all of those
stupid brackets and we'll talk. (see LISP)



From cbbrowne@acm.org Fri Dec  17 03:34:01 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id E810E3B75B3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 03:34:01 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 31702-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 17 Dec 2004 03:33:51 +0000 (GMT)
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2])
	by svr1.postgresql.org (Postfix) with ESMTP id C7D3C3CB9B2
	for <pgsql-sql@postgresql.org>; Fri, 17 Dec 2004 03:33:53 +0000 (GMT)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id CA6BE31959; Fri, 17 Dec 2004 04:33:52 +0100 (MET)
From: Christopher Browne <cbbrowne@acm.org>
X-Newsgroups: pgsql.sql
Subject: Re: plpgsql.. SELECT INTO ... WHERE FIELD LIKE
Date: 17 Dec 2004 03:33:49 GMT
Organization: cbbrowne Computing Inc
Lines: 14
Message-ID: <32f2ctF3laeu6U1@individual.net>
References: <001901c4e3ca$7c71aca0$a101a8c0@home.axiontech.com>
X-Draft-From: ("nntp+wolfe:pgsql.sql" 165)
X-message-flag: Outlook is rather hackable, isn't it?
X-Home-Page: http://www.cbbrowne.com/info/
X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.43 tagged_above=0 required=5 tests=AWL, INFO_TLD,
	UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200412/110
X-Sequence-Number: 19730

Try:
   SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || ''%'' LIMIT 1;

You append KEYWORD and a '%' together using ||.  You need to use
doubled quotes inside the quoted environment; one gets stripped off so
that the stored procedure will contain the query

   SELECT INTO RS ID FROM CUSTOMER WHERE FIRSTNAME LIKE KEYWORD || '%'' LIMIT 1;
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/sgml.html
C is almost a real language. (see assembler) Even the name sounds like
it's gone through  an optimizing  compiler.  Get  rid of  all of those
stupid brackets and we'll talk. (see LISP)