pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCasting with character and character varying
3+ messages / 3 participants
[nested] [flat]
* Casting with character and character varying
@ 2003-03-19 05:43 David Loh <david@bizsurf.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Loh @ 2003-03-19 05:43 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Casting with character and character varying
@ 2003-03-19 10:01 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 3+ messages in thread
From: Christoph Haller @ 2003-03-19 10:01 UTC (permalink / raw)
To: pgsql-sql; +Cc: david@bizsurf.com
>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Casting with character and character varying
@ 2003-03-19 14:59 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: David Loh <david@bizsurf.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jeff Eckermann @ 2003-03-19 14:59 UTC (permalink / raw)
To: David Loh <david@bizsurf.com>; pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-03-19 14:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-19 05:43 Casting with character and character varying David Loh <david@bizsurf.com>
2003-03-19 14:59 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2003-03-19 10:01 Re: Casting with character and character varying Christoph Haller <ch@rodos.fzk.de>
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