On Friday 10 December 2010 9:20:19 am Dmitriy Igrishin wrote: > Hey Adrian, > > 2010/12/10 Adrian Klaver <adrian.klaver@xxxxxxxxx> > > > On Friday 10 December 2010 8:51:19 am Alexander Farber wrote: > > > Please help, struggling since hours with this :-( > > > > > > I've created the following table (columns here and in the proc > > > sorted alphabetically) to acquire data copied from Oracle: > > > > > > # \d qtrack > > > Table "public.qtrack" > > > Column | Type | Modifiers > > > -------------+-----------------------------+--------------- > > > appsversion | character varying(30) | > > > beta_prog | character varying(20) | > > > category | character varying(120) | > > > catinfo | character varying(120) | > > > details | character varying(50) | > > > devinfo | character varying(4000) | > > > emailid | character varying(16) | > > > email | character varying(320) | > > > formfactor | character varying(10) | > > > id | character varying(20) | not null > > > imei | character varying(25) | > > > name | character varying(20) | > > > osversion | character varying(30) | > > > pin | character varying(12) | > > > qdatetime | timestamp without time zone | > > > copied | timestamp without time zone | default now() > > > Indexes: > > > "qtrack_pkey" PRIMARY KEY, btree (id) > > > > > > And for my "upsert" procedure I get the error: > > > > > > SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too > > > long for type character varying(16) > > > > > > CONTEXT: SQL statement "update qtrack set APPSVERSION = $1 , > > > BETA_PROG = $2 , CATEGORY = $3 , CATINFO = $4 , DETAILS = $5 , > > > DEVINFO = $6 , EMAIL = $7 , EMAILID = $8 , FORMFACTOR = $9 , ID = > > > $10 , IMEI = $11 , NAME = $12 , OSVERSION = $13 , PIN = $14 , > > > QDATETIME = $15 , COPIED = current_timestamp where ID = $10 " > > > PL/pgSQL function "qtrack_upsert" line 2 at SQL statement > > > > Looks like you got your EMAIL and EMAILID reversed. In your argument list > > EMAILID is 7th but it is getting the 8th variable, the reverse for EMAIL. > > Yes, but he refers arguments by name, rather than number. UPDATE statement > seems to be correct in the function definition. I am just looking at the CONTEXT message above and it showing EMAIL being assigned the $7 variable, which according to his argument list is _EMAILID. EMAIL and EMAILID are the only two fields where the variable number does not match the variable/argument numbers and are in fact reversed. So something is happening there and would explain the problem because you that would mean you are trying to stuff a 320 char field into a 16 char slot :) > > > > My "upsert" procedure is: > > > > > > create or replace function qtrack_upsert( > > > _APPSVERSION varchar, > > > _BETA_PROG varchar, > > > _CATEGORY varchar, > > > _CATINFO varchar, > > > _DETAILS varchar, > > > _DEVINFO varchar, > > > _EMAILID varchar, > > > _EMAIL varchar, > > > _FORMFACTOR varchar, > > > _ID varchar, > > > _IMEI varchar, > > > _NAME varchar, > > > _OSVERSION varchar, > > > _PIN varchar, > > > _QDATETIME timestamp > > > ) returns void as $BODY$ > > > begin > > > update qtrack set > > > APPSVERSION = _APPSVERSION, > > > BETA_PROG = _BETA_PROG, > > > CATEGORY = _CATEGORY, > > > CATINFO = _CATINFO, > > > DETAILS = _DETAILS, > > > DEVINFO = _DEVINFO, > > > EMAIL = _EMAIL, > > > EMAILID = _EMAILID, > > > FORMFACTOR = _FORMFACTOR, > > > ID = _ID, > > > IMEI = _IMEI, > > > NAME = _NAME, > > > OSVERSION = _OSVERSION, > > > PIN = _PIN, > > > QDATETIME = _QDATETIME, > > > COPIED = current_timestamp > > > where ID = _ID; > > > > > > if not found then > > > insert into qtrack ( > > > APPSVERSION, > > > BETA_PROG, > > > CATEGORY, > > > CATINFO, > > > DETAILS, > > > DEVINFO, > > > EMAIL, > > > EMAILID, > > > FORMFACTOR, > > > ID, > > > IMEI, > > > NAME, > > > OSVERSION, > > > PIN, > > > QDATETIME > > > ) values ( > > > _APPSVERSION, > > > _BETA_PROG, > > > _CATEGORY, > > > _CATINFO, > > > _DETAILS, > > > _DEVINFO, > > > _EMAIL, > > > _EMAILID, > > > _FORMFACTOR, > > > _ID, > > > _IMEI, > > > _NAME, > > > _OSVERSION, > > > _PIN, > > > _QDATETIME > > > ); > > > end if; > > > end; > > > $BODY$ language plpgsql; -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
