By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,735 Members | 1,384 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,735 IT Pros & Developers. It's quick & easy.

FUNCTION problem

P: n/a
Sky
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies

P: n/a
Sky <sk*@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2

P: n/a
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 ma*******@postgresql.org

Nov 23 '05 #3

P: n/a

Sky <sk*@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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #4

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.