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
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 |