pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

date and plpgsql error
2+ messages / 2 participants
[nested] [flat]

* date and plpgsql error
@ 2003-03-14 01:37  Christopher Smith <christopherl_smith@yahoo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Christopher Smith @ 2003-03-14 01:37 UTC (permalink / raw)
  To: 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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: date and plpgsql error
@ 2003-03-14 01:55  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Christopher Smith <christopherl_smith@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2003-03-14 01:55 UTC (permalink / raw)
  To: Christopher Smith <christopherl_smith@yahoo.com>; +Cc: pgsql-sql

On Thu, 13 Mar 2003, Christopher Smith wrote:

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

> 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

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

Umm, it seems to me that your insert is off on columns.  You're
inserting
 varchar, int, varchar, varchar, date, ...
into a table of
 varchar, varchar, varchar, date

I don't see an int anywhere in the table, so I'm not sure what you were
intending to do.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-03-14 01:55 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-14 01:37 date and plpgsql error Christopher Smith <christopherl_smith@yahoo.com>
2003-03-14 01:55 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

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