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 ; 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 ; 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 ; 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" To: 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
How in plpgsql use LIKE with a=20 variable?
 
let say I want to do this = query:
 
     SELECT=20 INTO RS id FROM customer WHERE firstname LIKE keyword% LIMIT=20 1;
 
keyword is a variable, in this case I = want to find=20 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=20 INT4 AS'
 
  DECLARE
    keyword ALIAS FOR=20 $1;
    RS RECORD;
 
  BEGIN
    SELECT = INTO RS id FROM=20 customer WHERE firstname like keyword% LIMIT 1;
    IF FOUND = THEN
      RETURN=20 RS.id;
    ELSE
       = RETURN=20 NULL;
    END IF;
 
  
 END'
LANGUAGE 'PLPGSQL';
 
------=_NextPart_000_0016_01C4E398.2F19CA50--