Jun Yang wrote:
> On Sat, Aug 1, 2009 at 7:53 PM, David Fetter <david@fetter.org
> <mailto:david@fetter.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.