RE : Re: Asking for some PL/pgSQL Tips

On Wed Aug 01 00:12:53 CEST 2001
Dr. Evil (drevil(at)sidereal(dot)kz) wrote :

Tankgirl, are you really really sure that you want to dynamicly add
columns to a table? You probably need to think through your data
definition if you are trying to do this, I think.

Well yes, I want to dynamically change the schema of a database... this
means at least altering tables adding and removing attributes.

I have chosen PL/pgSQL for my purpose, because I needed a procedural language
in order to check whether an attribute can be added or not (I have to make sure,
that the table exists, that there is no already another attribute with the same
name, etc ) .

But when I try to generate an ''ALTER TABLE'' dynamically it doesn't work.

I have also tried to insert directly the attribute into the pg_attribute
something like:

INSERT INTO pg_attribute(attrelid, attname, atttypid) values (20362, new_col, 23);

--> 20362 is the oid of the table where I want to insert the new attribute.
--> new_col is the name of the attribute I want to insert
--> 23 is the oid for INTEGER.

Well, this inserts the attribute in pg_inherits, but when I do

\d the_table_I_have_altered

new_col is not among the other attributes of the table.

By the way, my question is... As long as I do have to dynamically change the
schema of the database, which is the best way of doing it?

And also I wonder if there is anyway of createing an script in Unix that generates
another file.sql, executes it inside the database and finally removes it. Something
like the SPOOL/SPOOL OFF commands of sqlplus in Oracle that also lets you execute
an script from the database (@filename) .

I would be very grateful if someone tells me.

Stay Safe & Happy,

:* TankGirl