From: Christopher Smith <christopherl_smith@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: date and plpgsql error
Date: Thu, 13 Mar 2003 17:37:36 -0800 (PST)
Message-ID: <20030314013736.32799.qmail@web14107.mail.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: christopherl_smith@yahoo.com
Subject: Re: date and plpgsql error
In-Reply-To: <20030314013736.32799.qmail@web14107.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox