Thread: Casting with character and character varying

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.

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

--- David Loh <david@bizsurf.com> wrote:
>   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);

This last statement inserts every record from tableA
into temptable.  This follows from the PostgreSQL
"implicit from-clause" feature, whereby "select
tableA.FieldA" is equivalent to "select FieldA from
tableA".

What you want is to refer to "Rec.FieldA", which will
give you the single value that you are looking for.

__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com