pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Robby Slaughter <webmaster@robbyslaughter.com>
To: webmaster@robbyslaughter.com
Subject: pl/pgSQL and escaping LIKE clauses
Date: Mon, 25 Jun 2001 19:50:48 -0500
Message-ID: <EPEHLKLEHAHLONFOKNHNCELODBAA.webmaster@robbyslaughter.com> (raw)
In-Reply-To: <LNEOLDDDAKFHFMFOADPPCEDICAAA.wherring@mail.ifas.ufl.edu>

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



Message-ID: <EPEHLKLEHAHLONFOKNHNCELODBAA.webmaster@robbyslaughter.com>
Permalink:  ../EPEHLKLEHAHLONFOKNHNCELODBAA.webmaster@robbyslaughter.com/
Also on:    postgresql.org/message-id/EPEHLKLEHAHLONFOKNHNCELODBAA.webmaster@robbyslaughter.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: webmaster@robbyslaughter.com
  Subject: Re: pl/pgSQL and escaping LIKE clauses
  In-Reply-To: <EPEHLKLEHAHLONFOKNHNCELODBAA.webmaster@robbyslaughter.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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