Re: "Compact" and "complete" SQL

On 10.05.2010 21:16, dpage@pgadmin.org wrote:
> On Mon, May 10, 2010 at 3:20 PM, Erwin Brandstetter
> <brandstetter@falter.at>  wrote:
>
>> On 07.05.2010 21:21, dpage@pgadmin.org wrote:
>>
>>> Sorry - missed that. I generally prefer to only include SQL DDL for
>>> things that are non-default.
>>>
>> I generally agree. I see the "complete" variant as an option. The "compact"
>> (non-default SQL DDL) version is what would make my work easier.
>> However, at the time being we have a mixture. How would you define
>> "non-default"?
>>
> Anything where explicit DDL is required to recreate the object as it
> is. If the DDL is redundant (ie. it tries to set the value we get if
> we don't use it at all), then it should be omitted.
>

That's what we have been discussing. I would define "default" for the
current connection as what "SHOW ALL" returns.
Default settings of a connection are defined by host, user, database
(same parameters as displayed in the toolbar of the SQL window):
Plus, possibly default ACLs for the database / schema in pg 9.0. (Did i
get all sources?)

As it is now, there are many redundant statements / clauses included.
Examples:

CREATE TABLE foo
(
   foo_id serial NOT NULL,
   note text,
...
)
WITH (
   OIDS=FALSE        -- redundant, as OIDS=FALSE is the cluster-wide
default.
)

ALTER TABLE foo OWNER TO postgres;     -- redundant for the current user
postgres

CONSTRAINT ort_land_id_fk FOREIGN KEY (land_id)
       REFERENCES land (land_id) MATCH SIMPLE            -- "MATCH
SIMPLE" is default
       ON UPDATE CASCADE ON DELETE NO ACTION,

CREATE INDEX ort_ort_idx
   ON ort
   USING btree                -- redundant as it is default
   (ort);

etc.

On the other hand, if somebody wants to copy objects from one connection
to another (different host, user, ...) "complete" SQL DDL would be
pretty useful.

Regards
Erwin