Postgres Pro
Downloads
Home   >   mailing lists

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

plpgsql.. SELECT INTO ... WHERE FIELD LIKE

From
"Yudie"
Date:
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%'
 
 
====================================================
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;
 
  
 END'
LANGUAGE 'PLPGSQL';
 

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

From
Michael Fuhr
Date:
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/

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

From
Christopher Browne
Date:
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)

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

From
Christopher Browne
Date:
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)