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