date and plpgsql error

Поиск
Список
Период
Сортировка
От Christopher Smith
Тема date and plpgsql error
Дата
Msg-id 20030314013736.32799.qmail@web14107.mail.yahoo.com
обсуждение исходный текст
Ответы Re: date and plpgsql error  (Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Список pgsql-sql
<p>I am trying to insert a date into a table with type date.<p>CREATE TABLE member_billing_info (<br />userid
varchar(30)primary key,<br />creditcard_type varchar(30),<br />creditcard_number varchar(50),<br />****exp_date
date,<br/>first_name varchar(100),<br />last_name varchar(100),<br />street_address varchar(100),<br />city
varchar(100),<br/>state varchar(30),<br />zip_code varchar(30),<br />country varchar(100),<br />phone_number
varchar(30),<br/>email varchar(200),<br />date_entered timestamp,<br />last_update_time timestamp<br />);<br /><p>I get
errorslike this<p>WARNING:  Error occurred while executing PL/pgSQL function enrollpayee<br />WARNING:  line 27 at SQL
statement<br/>ERROR:  column "exp_date" is of type date but expression is of type character varying<br />        You
willneed to rewrite or cast the expression<br /><p>=== this is my function ======<p>CREATE or replace FUNCTION
enrollpayee(character varying, integer,character varying, character varying,<br />     text,character varying,character
varying,charactervarying,<br />     character varying,character varying,character varying,character varying,<br />    
charactervarying,character varying) RETURNS integer<br />    AS 'DECLARE<br />    <br /> strUserId ALIAS FOR $1;<br
/> intPlanIdALIAS FOR $2;<br /> strCardType ALIAS FOR $3;<br /> cryptCreditCard ALIAS FOR $4;<br /> strExpDate ALIAS
FOR$5;<br /> strFirstName ALIAS FOR $6;<br /> strLastName ALIAS FOR $7;<br /> strAddress ALIAS FOR $8; <br /> strCity
ALIASFOR $9;<br /> strState ALIAS FOR $10;<br /> strZipCode ALIAS FOR $11;<br /> strCountry ALIAS FOR $12;<br
/> strEmailALIAS FOR $13;<br /> strPhone ALIAS FOR $14;<p> v_Status integer :=1;<br /> v_LifeTime integer :=0;<br
/> v_Successinteger :=1; <br /> <br /> v_ExpDate date;<br />BEGIN<p> v_ExpDate := CAST(strExpDate as DATE);<p>INSERT
INTOmember_billing_info
values(strUserId,intPlanId,strCardType,cryptCreditCard,v_ExpDate,strFirstName,strLastName,strAddress,strCity,strState,strZipCode,strCountry,strPhone,strEmail,now(),now());<p><br
/>RETURNv_Success;<p>    <br />END;<p>'<br />    LANGUAGE plpgsql;<br />    <br />    ===========<p>the date text has
theformat of this "04/15/2003". I also tried "2003-04-15". and I also tried in the function argument list using the
typedate.<p>Nothing worked for me. Help.<p>thanks<p> <p> <p> <p><br /><hr size="1" />Do you Yahoo!?<br /><a
href="http://rd.yahoo.com/webhosting/mail_tagline/evt=7748/*http://webhosting.yahoo.com/ps/wh3/prod/">Yahoo!Web
Hosting</a>- establish your business online 

В списке pgsql-sql по дате отправления:

Предыдущее
От: Greg Stark
Дата:
Сообщение: Re: massive INSERT
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: date and plpgsql error