Postgres Pro
Downloads
Home   >   mailing lists

Thread: FUNCTION problem

FUNCTION problem

From
Sky
Date:
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.

Please help me. I'm gona Crazy ...
I would like to write that function
with plain Sql / LANGUAGE 'sql' /.

CIAO

--
Sky
sky AT sylvio .hu
Debian Group - Association of Hungarian Linux Users
Accessibility Project leader
gpg --keyserver hkp://pgp.mit.edu --recv-keys 314D1B0C
fingerprint = B211 E8C7 C6C2 18B2 9F30  ADF6 EDD9 A7FA 314D 1B0C

Re: FUNCTION problem

From
Tom Lane
Date:
Sky <sky@sylvio.hu> writes:
> SELECT user_id FROM person WHERE uid=$1 AND pwd=$2;

> ERROR:  Unable to identify an operator '=$' for types 'character' and
> 'integer

You need spaces:

 SELECT user_id FROM person WHERE uid= $1 AND pwd= $2;

This is fixed as of PG 7.4, IIRC.  Prior to that it's a feature not a
bug ;-) because we had a different definition of what an operator name
could be.

            regards, tom lane

Re: FUNCTION problem

From
Mike Mascari
Date:
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.

Works for me:

[test@lexus] CREATE TABLE person(
test(#  user_id SERIAL NOT NULL,
test(#  uid CHARACTER(20) NOT NULL,
test(#  pwd CHARACTER(20) NOT NULL,
test(#  PRIMARY KEY (user_id)
test(#  );
NOTICE:  CREATE TABLE will create implicit sequence
"person_user_id_seq" for "serial" column "person.user_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"person_pkey" for table "person"
CREATE TABLE
[test@lexus] CREATE FUNCTION getuserid (CHARACTER(20),CHARACTER(20))
test-# RETURNS SETOF INTEGER
test-# AS
test-# '
test'# SELECT user_id FROM person WHERE uid=$1 AND pwd=$2;
test'# '
test-# LANGUAGE 'sql';
CREATE FUNCTION
[test@lexus] select * from getuserid('Mike', 'Mascari');
  getuserid
-----------
(0 rows)
[test@lexus] insert into person (uid, pwd) values ('Mike', 'Mascari');
INSERT 447929 1
[test@lexus] select * from getuserid('Mike', 'Mascari');
  getuserid
-----------
          1
(1 row)
[test@lexus] select version();
                                                  version

---------------------------------------------------------------------------------------------------------
  PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.2 20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)


Mike Mascari

Re: FUNCTION problem

From
Greg Stark
Date:
Sky <sky@sylvio.hu> writes:

>   uid CHARACTER(20) NOT NULL,
>   pwd CHARACTER(20) NOT NULL,

Incidentally, are you sure you want character(20) ? The input will be padded
out to 20 characters with spaces. Usually people find varchar() more
convenient.

--
greg

Re: FUNCTION problem

From
Bill Moran
Date:
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