Re: plpgsql.. SELECT INTO ... WHERE FIELD LIKE

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/