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@bigfoot.com
Three days without programming and life becomes meaningless.