> > Hi all. > Recently I face some problem with casting character type variable and > varchar variable. > The situation was like: I had 2 table, on table A, the user_name is defined > as character(32), and table B uses varchar(32). I have 1 function and a > trigger to manipulate with these data. > > Here's the function: (NEW = tableB) > ------------------------------ > create or replace function prepaid () returns trigger as ' > declare Rec tableA%ROWTYPE; > > begin > if NEW.status != 2 then > return NEW; > else > select into Rec * from tableA where user_name = trim(trailing '' '' from > cast(NEW.user_name as varchar)) and user_type = ''T''; > if not found then > return NEW; > end if; > > insert into temptable values (tableA.FieldA); > end if; > return NEW; > end; > ' language 'plpgsql'; > ------------------------- > supposingly the insert will insert the value of field A in table into > temptable (declare as varchar(100)), instead of inserting single row, the > insert actually insert all data from tableA to temptable (if there's 10 row > in tableA, the insert statement will insert all to temptable), that's weird. > > Then i tried with cast(trim(trailing '' '' from NEW.user_name)::varchar as > text), and it's returns me with nothing (suppose there'll be 1 record > matched). > Don't know what's actually right now: If tableA uses character and tableB varchar, you'll have to trim the user_name from tableA not tableB, because varchar is already trimmed. But what you're doing within the function code is trimming a varchar field. Second is, what is "tableA.FieldA"? Is it a column name of tableA? Looks that way, because I can't see a variable of this name. I'm not sure what happens on an insert statement like this, but it's very well possible this causes all row-columns FieldA from tableA to be inserted into temptable. And it would be useful to see the CREATE TRIGGER statement too.
Regards, Christoph ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org