Re: pad column with leading zeros or space

On Wed, 23 Jan 2002, Johnson, Shaunn wrote:

In general, Tom's right that it's probably best to do this in the front
end, but...

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

If you want it to really be stored with all the 0's, a trigger
would probably do what you want. If you want it converted
to the text string with 0's when a select is done, you'd probably
want a view to do the manipulation for you.

(trigger example - mostly untested:)
create table foo (
member_id text
);

create function foofunc() returns opaque as
'begin
NEW.member_id := lpad(NEW.member_id, 10, ''0'');
return NEW;
end;'
language 'plpgsql';

create trigger t1 before insert or update on foo
for each row execute procedure foofunc();