create definiton

pintman@gmx.de (Marco) writes:

is it possible to find out, which create-statement i have used to
create a certain table? if so, how?

pg_dump -s -t tablename dbname

will reconstruct the table schema for you.

regards, tom lane

On Sat, Jun 15, 2002 at 06:36:29AM -0700,
Marco <pintman@gmx.de> wrote
a message of 4 lines which said:

is it possible to find out, which create-statement i have used to
create a certain table? if so, how?

Not the exact one but "pg_dump --schema-only $DB" is close enough.

Marco wrote:

is it possible to find out, which create-statement i have used to
create a certain table? if so, how?

Yes, pg_dump has options to dump only the schema for an individual
table.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Yeah, but the gotcha there is that any foreign keys are not saved as a
FOREIGN KEY statement, just as the INSERT/UPDATE trigger that enforces the
foreign key. So if you dump the schema, drop the table (which deletes the
foreign keys in the foreign keyed tables), alter the dump and reload: the
triggers to enforce the foreign key validation that reside in the tables the
foreign key is keyed into do not get recreated, so your referential
integrity is only enforced WRT changes to your table reloaded from the
dump, and NOT enforced from the tables the keys go into.

Please correct me if I am wrong, because if there is a better way I would
like to know, I deal with specs changing all the time and have to drop
tables to change them from NULL to NOT NULL etc.

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com

On Mon, 2002-06-17 at 12:43, terry@greatgulfhomes.com wrote:

Yeah, but the gotcha there is that any foreign keys are not saved as a
FOREIGN KEY statement, just as the INSERT/UPDATE trigger that enforces the
foreign key. So if you dump the schema, drop the table (which deletes the
foreign keys in the foreign keyed tables), alter the dump and reload: the
triggers to enforce the foreign key validation that reside in the tables the
foreign key is keyed into do not get recreated, so your referential
integrity is only enforced WRT changes to your table reloaded from the
dump, and NOT enforced from the tables the keys go into.

I believe this is incorrect. A schema dump will include the triggers.
from my latest schema dump (this morning):
<snip>
--
-- TOC Entry ID 373 (OID 1133843)
--
-- Name: "RI_ConstraintTrigger_1133842" Type: TRIGGER Owner: tjenkins
--

CREATE CONSTRAINT TRIGGER "mandatorytraininggroupid_mandat" AFTER DELETE
ON "mandatorytraininggroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH
ROW EXECUTE PROCEDURE "RI_FKey_cascade_del"
('mandatorytraininggroupid_mandat', 'mandatorytraining',
'mandatorytraininggroup', 'UNSPECIFIED', 'mandatorytraininggroupid',
'mandatorytraininggroupid');

<snip>
--
-- TOC Entry ID 460 (OID 1133543)
--
-- Name: "RI_ConstraintTrigger_1133542" Type: TRIGGER Owner: tjenkins
--

CREATE CONSTRAINT TRIGGER "educationemployee_employee_fk" AFTER UPDATE
ON "employee" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_upd" ('educationemployee_employee_fk',
'education', 'employee', 'FULL', 'educationemployee', 'employeeid');

<snip>

Please correct me if I am wrong, because if there is a better way I would
like to know, I deal with specs changing all the time and have to drop
tables to change them from NULL to NOT NULL etc.

)
--

Tom Jenkins
Development InfoStructure
http://www.devis.com

There are 2 sides to a foreign key e.g.
if invoices.user_id is foreign keyed to users.user_id

When you add an invoice, invoices trigger(s) check to make sure the user_id
is valid in the users table. (call it trigger set A)
When you update/delete a user in users, users triggers make sure that the
user_id is not used in the invoices table, if it is the trigger(s) stop you
from update/deleting that user_id in the users table (call it trigger set B)

When you pg_dump a table, the dump contains the SQL statements to create
trigger set A, but NOT trigger set B.

When you drop the table both trigger set A and trigger set B are deleted.

Then, when I alter the schema and reload the table, I have the issue that
only trigger set A is reloaded, hence the referential integrity is only
enforced from the table invoices, not from changes to the table users.

I can give you a more precise example for illustration if you like...

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com