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(at)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(at)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(at)lexus] select * from getuserid('Mike', 'Mascari');
getuserid
-----------
(0 rows)
[test(at)lexus] insert into person (uid, pwd) values ('Mike', 'Mascari');
INSERT 447929 1
[test(at)lexus] select * from getuserid('Mike', 'Mascari');
getuserid
-----------
1
(1 row)
[test(at)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