plpgsql.. SELECT INTO ... WHERE FIELD LIKE

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

plpgsql.. SELECT INTO ... WHERE FIELD LIKE
4+ messages / 3 participants
[nested] [flat]

* plpgsql.. SELECT INTO ... WHERE FIELD LIKE
@ 2004-12-16 23:53  Yudie <yudie@axiontech.com>
  0 siblings, 3 replies; 4+ messages in thread

From: Yudie @ 2004-12-16 23:53 UTC (permalink / raw)
  To: pgsql-sql

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';

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: plpgsql.. SELECT INTO ... WHERE FIELD LIKE
@ 2004-12-17 00:46  Michael Fuhr <mike@fuhr.org>
  parent: Yudie <yudie@axiontech.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Michael Fuhr @ 2004-12-17 00:46 UTC (permalink / raw)
  To: Yudie <yudie@axiontech.com>; +Cc: pgsql-sql

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/



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: plpgsql.. SELECT INTO ... WHERE FIELD LIKE
@ 2004-12-17 03:08  Christopher Browne <cbbrowne@acm.org>
  parent: Yudie <yudie@axiontech.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Christopher Browne @ 2004-12-17 03:08 UTC (permalink / raw)
  To: pgsql-sql

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)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: plpgsql.. SELECT INTO ... WHERE FIELD LIKE
@ 2004-12-17 03:33  Christopher Browne <cbbrowne@acm.org>
  parent: Yudie <yudie@axiontech.com>
  2 siblings, 0 replies; 4+ messages in thread

From: Christopher Browne @ 2004-12-17 03:33 UTC (permalink / raw)
  To: pgsql-sql

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)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2004-12-17 03:33 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-16 23:53 plpgsql.. SELECT INTO ... WHERE FIELD LIKE Yudie <yudie@axiontech.com>
2004-12-17 00:46 ` Michael Fuhr <mike@fuhr.org>
2004-12-17 03:08 ` Christopher Browne <cbbrowne@acm.org>
2004-12-17 03:33 ` Christopher Browne <cbbrowne@acm.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox