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).
If any of you guys willing to help me out, I'll apprepriate it. Or you may
point me to some postgresql casting tutorial.
Thanks.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
