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