date and plpgsql error

From: Christopher Smith <christopherl_smith(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: date and plpgsql error
Date: 2003-03-14 01:37:36
Message-ID: 20030314013736.32799.qmail@web14107.mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


I am trying to insert a date into a table with type date.

CREATE TABLE member_billing_info (
userid varchar(30) primary key,
creditcard_type varchar(30),
creditcard_number varchar(50),
****exp_date date,
first_name varchar(100),
last_name varchar(100),
street_address varchar(100),
city varchar(100),
state varchar(30),
zip_code varchar(30),
country varchar(100),
phone_number varchar(30),
email varchar(200),
date_entered timestamp,
last_update_time timestamp
);

I get errors like this

WARNING: Error occurred while executing PL/pgSQL function enrollpayee
WARNING: line 27 at SQL statement
ERROR: column "exp_date" is of type date but expression is of type character varying
You will need to rewrite or cast the expression

=== this is my function ======

CREATE or replace FUNCTION enrollpayee (character varying, integer,character varying, character varying,
text,character varying,character varying,character varying,
character varying,character varying,character varying,character varying,
character varying,character varying) RETURNS integer
AS 'DECLARE

strUserId ALIAS FOR $1;
intPlanId ALIAS FOR $2;
strCardType ALIAS FOR $3;
cryptCreditCard ALIAS FOR $4;
strExpDate ALIAS FOR $5;
strFirstName ALIAS FOR $6;
strLastName ALIAS FOR $7;
strAddress ALIAS FOR $8;
strCity ALIAS FOR $9;
strState ALIAS FOR $10;
strZipCode ALIAS FOR $11;
strCountry ALIAS FOR $12;
strEmail ALIAS FOR $13;
strPhone ALIAS FOR $14;

v_Status integer :=1;
v_LifeTime integer :=0;
v_Success integer :=1;

v_ExpDate date;
BEGIN

v_ExpDate := CAST(strExpDate as DATE);

INSERT INTO member_billing_info values(strUserId,intPlanId,strCardType,cryptCreditCard,v_ExpDate,strFirstName,strLastName,strAddress,strCity,strState,strZipCode,strCountry,strPhone,strEmail,now(),now());

RETURN v_Success;


END;

'
LANGUAGE plpgsql;

===========

the date text has the format of this "04/15/2003". I also tried "2003-04-15". and I also tried in the function argument list using the type date.

Nothing worked for me. Help.

thanks

---------------------------------
Do you Yahoo!?
Yahoo! Web Hosting - establish your business online

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2003-03-14 01:55:01 Re: date and plpgsql error
Previous Message Greg Stark 2003-03-14 00:20:36 Re: massive INSERT