X-Original-To: pgsql-sql@postgresql.org Received: from web14107.mail.yahoo.com (web14107.mail.yahoo.com [216.136.172.137]) by postgresql.org (Postfix) with SMTP id 14A2F47580B for ; Thu, 13 Mar 2003 20:37:33 -0500 (EST) Message-ID: <20030314013736.32799.qmail@web14107.mail.yahoo.com> Received: from [66.93.249.228] by web14107.mail.yahoo.com via HTTP; Thu, 13 Mar 2003 17:37:36 PST Date: Thu, 13 Mar 2003 17:37:36 -0800 (PST) From: Christopher Smith Subject: date and plpgsql error To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1952800915-1047605856=:32649" X-Archive-Number: 200303/347 X-Sequence-Number: 12386 --0-1952800915-1047605856=:32649 Content-Type: text/plain; charset=us-ascii 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 --0-1952800915-1047605856=:32649 Content-Type: text/html; charset=us-ascii

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 --0-1952800915-1047605856=:32649--