Received: from mail.utexas.edu (wb1-a.mail.utexas.edu [128.83.126.134]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5Q0ofa12754 for ; Mon, 25 Jun 2001 20:50:41 -0400 (EDT) (envelope-from webmaster@robbyslaughter.com) Received: (qmail 7368 invoked by uid 0); 26 Jun 2001 00:50:37 -0000 Received: from adsl-64-218-162-208.dsl.austtx.swbell.net (HELO robmanbox) (64.218.162.208) by umbs-smtp-1 with SMTP; 26 Jun 2001 00:50:37 -0000 Reply-To: From: "Robby Slaughter" To: Subject: pl/pgSQL and escaping LIKE clauses Date: Mon, 25 Jun 2001 19:50:48 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 Importance: Normal X-Archive-Number: 200106/98 X-Sequence-Number: 1654 I'm having trouble getting LIKE clauses to work correctly inside a plpgSQL function. Here's my table: id | val ----+------------- 1 | hello 2 | there 3 | everyone Here's my function: CREATE FUNCTION intable(char) RETURNS INTEGER AS ' DECLARE input ALIAS FOR $1; temp INTEGER; BEGIN SELECT INTO temp id FROM test WHERE val LIKE ''input%''; RAISE NOTICE ''Value of temp is %'',temp; RETURN temp; END; ' LANGUAGE 'plpgsql'; I should be able to SELECT('hello') and get back 1, correct? No matter what I put in as a parameter, it always returns null. If I change the LIKE clause to read "...LIKE ''hello%''" it does in fact work. Or if I scrap the LIKE clause and have it read something such as ".... id = input" (if input is an integer) it also works fine. Any thoughts? Thanks, Robby