Re: FUNCTION problem

Sky wrote:
> HI everybody !
>
> I have a problem, but I don't know the solution:
>
> CREATE TABLE person(
>  user_id SERIAL NOT NULL,
>  uid CHARACTER(20) NOT NULL,
>  pwd CHARACTER(20) NOT NULL,
>  PRIMARY KEY (user_id)
>  );
>
> OK, That's right...
>
> CREATE FUNCTION getuserid (CHARACTER(20),CHARACTER(20))
> RETURNS SETOF INTEGER
> AS
> '
> SELECT user_id FROM person WHERE uid=$1 AND pwd=$2;
> '
> LANGUAGE 'sql';
>
> :-(
>
> ERROR:  Unable to identify an operator '=$' for types 'character' and
> 'integer
> You will have to retype this query using an explicit cast.

You don't state what version of Postgres you're using, but I'll bet that
it's 7.3 or older.

Be a little more liberal with spaces to seperate the tokens in your
statement, i.e.:
SELECT user_id FROM person WHERE uid = $1 AND pwd = $2;

It seems to me that 7.3 and older don't parse quite as intelligently
as 7.4 does (which would explain why other people are saying "it works
for me")  What appears to be happening is that Postgres 7.3 looks at
uid=$1 and breaks it down into uid =$ 1, but (unless you created one)
it doesn't know anything about how to use =$ as a comparison, so it
throws an error.

7.4 seems to get this right more often, but that may be a bug in
the other direction ... I mean, what if you defind a =$ operator and
really want to compare uid =$ 1?

I think the real solution is to write your SQL so it's unambiguious
to the parser.  I saw this as a suggestion for C programming a few
years ago, that you always seperate tokens with space (even if not
strictly necessary) to make it unambiguous to the parser, as well as
easier for humans to read.  I think it's good advice all around.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com