Typecasting in SQL functions

From: Konstantinos Vassiliadis <vassilik(at)p22(dot)cs(dot)man(dot)ac(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: Typecasting in SQL functions
Date: 1998-07-14 12:34:04
Message-ID: Pine.LNX.3.95.980714133237.314E-100000@p22.cs.man.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,
I have the following

create table reference
(author text,
journal text);

create table subref
(ref reference);

I want to insert a tuple into relation 'subref'. I spotted in the mailing
list that Nicolas Caillaud created a function to insert values to his
relation 'book' so I thought I tried the same

create function add_ref(text,text) returns reference
as 'select * from reference
where author=$1 and journal=$2'
language 'sql';

I get the following
ERROR: There is no operator '=$' for types 'text' and 'int4'
You will either have to retype this query using an explicit cast,
or you will have to define the operator using CREATE OPERATOR

Obviously it confuses author=$1...
I have looked at the documenation (Ch 4. 'Extending SQL Functions'
PostgreSQL Programmer's Guide) where they typecast for the function
new_emp().

I have tried
<same query>
where author=::$1...

and get the same error message.

Can somebody help me on typecasting?

Kostas Vassiliadis

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Paulo Trezentos 1998-07-14 12:42:47 Insert query
Previous Message Herouth Maoz 1998-07-14 11:53:24 Re: [SQL] #define in SQL?