Use a view:
create view v_devel as
select lpad(bp_member_id::text, 10, '0') as bp_member_id, ... from
t_devel;
-----Original Message-----
From: Johnson, Shaunn [mailto:SJohnson6@bcbsm.com]
Sent: Wednesday, January 23, 2002 12:32 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pad column with leading zeros or space
--You're right, Tom.
--I've been toying around with the perl aspect of
padding what I needed with zeros and spaces, but either
way, it seems like I'm just going to be pained by this
exercise.
--For example, I'm testing the following:
[snip test table]
drop table t_devel;
CREATE TABLE t_devel (
"bp_member_id" int,
"bp_disease_id" int,
"bp_measure_id" int,
"bp_end_perios_id" int
);
grant select on t_devel to public;
[/snip]
[snip from command]
bcn=> insert into t_devel values (009834,23,3445,523);
INSERT 689881850 1
bcn=> select * from t_devel;
bp_member_id | bp_disease_id | bp_measure_id | bp_end_perios_id
--------------+---------------+---------------+------------------
9834 | 23 | 3445 | 523
(1 row)
bcn=> select lpad(bp_member_id:text, 10, '0') as bp_member_id from
t_devel;
ERROR: parser: parse error at or near ":"
bcn=> select lpad(bp_member_id::text, 10, '0') as bp_member_id from
t_devel;
bp_member_id
--------------
0000009834
(1 row)
[/snip]
This is the results that I want, but how do you *make* the table
keep the column attribute of things like lpad, rpad, etc ...
I mean, when I define the table, can't I just say something like:
[a guess]
create table foo (
member_id lpad(10, '0')
);
[/snip guess]
I know this doesn't work, but that's sorta what I'm going for.
Or, if it's not a current function by itself, can I develop
something that will always do that for each type, function and
attribute and return what I want?
Yeah ... seems like I'm on the 'gimme, gimmie, gimme' kick today ...
sorry 'bout that ...
Thanks all!
-X
-----Original Message-----
From: Tom Lane [ mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us> ]
> I am trying to create a table where I need to pad certain
> columns. ...
> Yeah, well, I don't know why a delimited format wouldn't
> help the clients, but it seems that they've hardcode
> their process so that the text dump will look *exactly*
> as specified.
Rather than hacking the database representation into some unnatural
choice of datatypes, I'd suggest you keep the table declaration clean
and work on producing the requested output format during the dump.
A simple sed, awk, or Perl script to postprocess a dump file might
well be the cleanest answer.
regards, tom lane