From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: david@bizsurf.com
Subject: Re: Casting with character and character varying
Date: Wed, 19 Mar 2003 11:01:56 +0100
Message-ID: <3E784014.42B14B7B@rodos.fzk.de> (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).
>
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
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: ch@rodos.fzk.de, david@bizsurf.com
Subject: Re: Casting with character and character varying
In-Reply-To: <3E784014.42B14B7B@rodos.fzk.de>
* 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