Skip site navigation (1) Skip section navigation (2)

pl/pgSQL and escaping LIKE clauses

From: "Robby Slaughter" <webmaster(at)robbyslaughter(dot)com>
To: <webmaster(at)robbyslaughter(dot)com>
Subject: pl/pgSQL and escaping LIKE clauses
Date: 2001-06-26 00:50:48
Message-ID: EPEHLKLEHAHLONFOKNHNCELODBAA.webmaster@robbyslaughter.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-sql
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

In response to

pgsql-novice by date

Next:From: Paul AndreDate: 2001-06-26 13:17:27
Subject: Hard limit on result handles reached ...
Previous:From: William HerringDate: 2001-06-26 00:08:39
Subject: select with multiple occurences in same table

pgsql-sql by date

Next:From: Markus WagnerDate: 2001-06-26 05:52:48
Subject: loosing connection after function call
Previous:From: William HerringDate: 2001-06-26 00:08:39
Subject: select with multiple occurences in same table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group