Re: FUNCTION problem

Mike Mascari <mascarm@mascari.com> · Fri, 02 Apr 2004 02:26:07 -0500

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org