| 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: | Whole Thread | Raw Message | 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 |