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.

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