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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice pgsql-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

Browse pgsql-novice by date

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

Browse pgsql-sql by date

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