Re: Can I add a super table to existing tables?

Jun Yang wrote:
> On Sat, Aug 1, 2009 at 7:53 PM, David Fetter <david(at)fetter(dot)org
> <mailto:david(at)fetter(dot)org>> wrote:
>
> On Sat, Aug 01, 2009 at 06:28:54PM -0700, Jun Yang wrote:
> > Hi all,
> >
> > I want to add some common columns to all of my tables.
>
> Your case may be very special, but offhand, this sounds like a very
> bad idea. What task is it you're trying to accomplish?
>
>
> Very simple. I have columns update_by and updated_at that I want
> every table to have but don't want to add them one by one.

for each $name in (select table_name from information_schema.tables
where table_schema='public') do
alter table $name add column update_by oid, updated_at timestamp;

(no, thats not SQL, its pseudo-code, do this in whatever sort of
scripting or programming language you usually use)

like, in bash scripting...

for $name in (`psql -t -c "select table_name from
information_schema.tables where table_schema='public'") do
psql -c "alter table $name add column update_by integer
references users(id), updated_at timestamp default now()"
done;

of course, you'd be better off doing this in perl, python, php, or
whatever you normally program in so that you weren't launching so many
seperate connections.