From: Christopher Browne <cbbrowne@acm.org>
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
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

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)