Functions with text parameters

From: Stoffel van Aswegen <Stoffelva(at)gmsi(dot)co(dot)za>
To: "'pgsql-novice(at)postgresql(dot)org'" <pgsql-novice(at)postgresql(dot)org>
Subject: Functions with text parameters
Date: 2000-09-27 05:58:35
Message-ID: 9D8BB61A24FCD0118E83080036CE9603925CFB@NTSERVER01
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I have trouble creating a function that takes text parameters.

I have a table (T1) with one field (code varchar(10)).
I want to create a function as follows:

CREATE FUNCTION test (varchar(10))
RETURNS int
AS 'SELECT COUNT(*) FROM T1 WHERE CODE=\'$1\''
LANGUAGE 'SQL'

pqsl complained about a "parsing error before (" - I assume it's the first (
of (10).

So it tried: "CREATE FUNCTION test (text)...."
psql accepted the function definition and it runs, but if the value of code
is 'abc' and I run "SELECT test('abc')", the function returns 0 (indicating
that no rows were found).

I did not try defining 'code' as 'text' (in table T1).

1. Why does the function not return the correct result? Is it because
the types of the parameter and the field are different?
2. How can I create the function by specifying the parameter as
varchar(10)? Should I create a user defined type that is equivalent to
varchar(10) and use that type in the function and table definitions?
3. What is the implication of using the 'text' type instead of
'varchar' in tables?

Stoffel van Aswegen
stoffelva(at)bigfoot(dot)com

Three days without programming and life becomes meaningless.

Browse pgsql-novice by date

  From Date Subject
Next Message Bo Dahlstrom 2000-09-27 08:41:04 Problem with initdb
Previous Message Joel Mc Graw 2000-09-27 02:25:03 Re: trigger to run external script