From: David Loh <david@bizsurf.com>
To: pgsql-sql@postgresql.org
Subject: Casting with character and character varying
Date: Wed, 19 Mar 2003 13:43:48 +0800
Message-ID: <007001c2edda$848c8410$b7d273cb@davidloh> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: david@bizsurf.com
Subject: Re: Casting with character and character varying
In-Reply-To: <007001c2edda$848c8410$b7d273cb@davidloh>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox