From 2b84cbb60f6ff6cb58d42dff026aaf0b2e9ca8ab Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Sat, 29 Jan 2000 16:58:54 +0000 Subject: A few minor psql enhancements Initdb help correction Changed end/abort to commit/rollback and changed related notices Commented out way old printing functions in libpq Fixed a typo in alter table / alter column --- doc/src/sgml/libpq.sgml | 64 ++++---------- doc/src/sgml/ref/abort.sgml | 10 +-- doc/src/sgml/ref/alter_table.sgml | 171 ++++++++++++-------------------------- doc/src/sgml/ref/begin.sgml | 23 ++--- doc/src/sgml/ref/commit.sgml | 9 +- doc/src/sgml/ref/end.sgml | 10 +-- doc/src/sgml/ref/psql-ref.sgml | 25 +++--- doc/src/sgml/ref/rollback.sgml | 13 +-- doc/src/sgml/ref/update.sgml | 4 +- 9 files changed, 118 insertions(+), 211 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index 3f9738b4626..7543e9add65 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -274,8 +274,8 @@ PostgresPollingStatusType *PQconnectPoll(PQconn *conn) - To begin, call conn=PQconnectStart("<connection_info_string>"). If - conn is NULL, then libpq has been unable to allocate a new PGconn + To begin, call conn=PQconnectStart("<connection_info_string>"). + If conn is NULL, then libpq has been unable to allocate a new PGconn structure. Otherwise, a valid PGconn pointer is returned (though not yet representing a valid connection to the database). On return from PQconnectStart, call status=PQstatus(conn). If status equals @@ -1019,6 +1019,9 @@ const char * PQcmdTuples(const PGresult *res); Oid PQoidValue(const PGresult *res); + The type Oid and the constant Invalid + will be defined if you include the libpq + header file. They will both be some integer type. @@ -1031,7 +1034,8 @@ Oid PQoidValue(const PGresult *res); const char * PQoidStatus(const PGresult *res); -The function is deprecated in favor of PQoidValue. +The function is deprecated in favor of PQoidValue +and is not thread-safe. @@ -1045,57 +1049,25 @@ void PQprint(FILE* fout, /* output stream */ const PGresult *res, const PQprintOpt *po); -struct _PQprintOpt { - pqbool header; /* print output field headings and row count */ - pqbool align; /* fill align the fields */ - pqbool standard; /* old brain dead format */ - pqbool html3; /* output html tables */ - pqbool expanded; /* expand tables */ - pqbool pager; /* use pager for output if needed */ +struct { + int header; /* print output field headings and row count */ + int align; /* fill align the fields */ + int standard; /* old brain dead format */ + int html3; /* output html tables */ + int expanded; /* expand tables */ + int pager; /* use pager for output if needed */ char *fieldSep; /* field separator */ char *tableOpt; /* insert to HTML <table ...> */ char *caption; /* HTML <caption> */ char **fieldName; /* null terminated array of replacement field names */ -} +} PQprintOpt; -This function is intended to replace PQprintTuples(), which is -now obsolete. The psql program uses -PQprint() to display query results. +This function was formerly used by psql +to print query results, but this is no longer the case and this +function is no longer supported. - - -PQprintTuples - Prints out all the tuples and, optionally, the - attribute names to the specified output stream. - -void PQprintTuples(const PGresult *res, - FILE *fout, /* output stream */ - int printAttName,/* print attribute names or not*/ - int terseOutput, /* delimiter bars or not?*/ - int width); /* width of column, variable width if 0*/ - - - - - - -PQdisplayTuples - Prints out all the tuples and, optionally, the - attribute names to the specified output stream. - -void PQdisplayTuples(const PGresult* res, - FILE *fout, /* output stream */ - int fillAlign, /* space fill to align columns */ - const char *fieldSep, /* field separator */ - int printHeader, /* display headers? */ - int quiet); /* suppress print of row count at end */ - -PQdisplayTuples() was intended to supersede -PQprintTuples(), and -is in turn superseded by PQprint(). - diff --git a/doc/src/sgml/ref/abort.sgml b/doc/src/sgml/ref/abort.sgml index e97654a0778..c31fcb2acbf 100644 --- a/doc/src/sgml/ref/abort.sgml +++ b/doc/src/sgml/ref/abort.sgml @@ -1,5 +1,5 @@ @@ -52,7 +52,7 @@ ABORT [ WORK | TRANSACTION ] -ABORT +ROLLBACK @@ -62,8 +62,8 @@ ABORT -NOTICE: UserAbortTransactionBlock and not in in-progress state -ABORT +NOTICE: ROLLBACK: no transaction in progress +ROLLBACK @@ -130,7 +130,7 @@ ABORT WORK; SQL92 - This command is a Postgres extension present + This command is a PostgreSQL extension present for historical reasons. ROLLBACK is the SQL92 equivalent command. diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index bb49772e8ee..54d06e5ce55 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1,5 +1,5 @@ @@ -23,11 +23,14 @@ Postgres documentation 1999-07-20 -ALTER TABLE table - [ * ] ADD [ COLUMN ] column table [ * ] + ADD [ COLUMN ] column type -ALTER TABLE table - [ * ] RENAME [ COLUMN ] column TO table [ * ] + ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT } +ALTER TABLE table [ * ] + RENAME [ COLUMN ] column TO newcolumn ALTER TABLE table RENAME TO newtable @@ -82,7 +85,7 @@ ALTER TABLE table newtable - New name for an existing column. + New name for the table. @@ -101,9 +104,7 @@ ALTER TABLE table - -ALTER - + ALTER Message returned from column or table renaming. @@ -112,20 +113,7 @@ ALTER - -NEW - - - - Message returned from column addition. - - - - - - -ERROR - + ERROR Message returned if table or column is not available. @@ -146,9 +134,12 @@ ERROR ALTER TABLE changes the definition of an existing table. - The new columns and their types are specified in the same style - and with the the same restrictions as in CREATE TABLE. - The RENAME clause causes the name of a table or column + The ADD COLUMN form adds a new column to the table + using the same syntax as . The ALTER COLUMN form + allows you to set or remove the default for the column. Note that defaults + only apply to newly inserted rows. + The RENAME clause causes the name of a table or column to change without changing any of the data contained in the affected table. Thus, the table or column will remain of the same type and size after this command is @@ -189,13 +180,12 @@ SELECT NewColumn FROM SuperClass - For efficiency reasons, default values for added attributes are - not placed in existing instances of a class. - That is, existing instances will have NULL values in the new - attributes. If non-NULL values are desired, a subsequent - UPDATE query - () - should be run. + In the current implementation, default and constraint clauses for the + new column will be ignored. You can use the SET DEFAULT + form of ALTER TABLE to set the default later. + (You will also have to update the already existing rows to the + new default value, using .) @@ -248,87 +238,38 @@ ALTER TABLE distributors RENAME TO suppliers; 1998-04-15 - - SQL92 - - - ALTER TABLE/RENAME - is a Postgres language extension. - + SQL92 + + The ADD COLUMN form is compliant with the exception that + it does not support defaults and constraints, as explained above. + The ALTER COLUMN form is in full compliance. + - - SQL92 specifies some additional capabilities for ALTER TABLE - statement which are not yet directly supported by - Postgres: - - - - - - -ALTER TABLE table ALTER [ - COLUMN ] column - SET DEFAULT default -ALTER TABLE table ALTER [ - COLUMN ] column - ADD [ CONSTRAINT >constrain> ] table-constraint - - - - - Puts the default value or constraint specified into the - definition of column in the table. - See CREATE TABLE for the - syntax of the default and table-constraint clauses. - If a default clause already exists, it will be replaced by - the new definition. If any constraints on this column already - exist, they will be retained using a boolean AND with the new - constraint. - - - - Currently, to set new default constraints on an existing column - the table must be recreated and reloaded: - - -CREATE TABLE temp AS SELECT * FROM distributors; -DROP TABLE distributors; -CREATE TABLE distributors ( - did DECIMAL(3) DEFAULT 1, - name VARCHAR(40) NOT NULL, - city VARCHAR(30) -); -INSERT INTO distributors SELECT * FROM temp; -DROP TABLE temp; - - - - + + SQL92 specifies some additional capabilities for ALTER TABLE + statement which are not yet directly supported by PostgreSQL: - - - -ALTER TABLE table - DROP DEFAULT default -ALTER TABLE table - DROP CONSTRAINT constraint { RESTRICT | CASCADE } + + + + +ALTER TABLE table ADD table constraint definition +ALTER TABLE table DROP CONSTRAINT constraint { RESTRICT | CASCADE } - Removes the default value specified by default or the rule - specified by constraint from the definition of a table. - If RESTRICT is specified only a constraint with no dependent - constraints can be destroyed. - If CASCADE is specified, Any constraints that are dependent on - this constraint are also dropped. + Adds or removes a table constraint (such as a check constraint, + unique constraint, or foreign key constraint). To create + or remove a unique constraint, create or drop a unique index, + respectively (see ). + To change other kinds of constraints you need to recreate + and reload the table, using other parameters to the + + command. - - Currently, to remove a default value or constraints on an - existing column the table must be recreated and reloaded: - + For example, to drop any constraints on a table distributors: CREATE TABLE temp AS SELECT * FROM distributors; DROP TABLE distributors; @@ -342,23 +283,14 @@ DROP TABLE temp; -ALTER TABLE table - DROP [ COLUMN ] column { RESTRICT | CASCADE } +ALTER TABLE table DROP [ COLUMN ] column { RESTRICT | CASCADE } Removes a column from a table. - If RESTRICT is specified only a column with no dependent - objects can be destroyed. - If CASCADE is specified, all objects that are dependent on - this column are also dropped. - - - Currently, to remove an existing column the table must be recreated and reloaded: - CREATE TABLE temp AS SELECT did, city FROM distributors; DROP TABLE distributors; @@ -373,6 +305,13 @@ DROP TABLE temp; + + + + The clauses to rename columns and tables are PostgreSQL + extensions. SQL92 does not provide for them. + + diff --git a/doc/src/sgml/ref/begin.sgml b/doc/src/sgml/ref/begin.sgml index a6e45aaadb1..1ee645c7711 100644 --- a/doc/src/sgml/ref/begin.sgml +++ b/doc/src/sgml/ref/begin.sgml @@ -1,5 +1,5 @@ @@ -72,7 +72,7 @@ BEGIN -NOTICE: BeginTransactionBlock and not in default state +NOTICE: BEGIN: already a transaction in progress @@ -95,7 +95,7 @@ NOTICE: BeginTransactionBlock and not in default state - By default, Postgres executes transactions + By default, PostgreSQL executes transactions in unchained mode (also known as autocommit in other database systems). @@ -116,7 +116,7 @@ NOTICE: BeginTransactionBlock and not in default state The default transaction isolation level in - Postgres + PostgreSQL is READ COMMITTED, where queries inside the transaction see only changes committed before query execution. So, you have to use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE @@ -128,7 +128,7 @@ NOTICE: BeginTransactionBlock and not in default state - If the transaction is committed, Postgres + If the transaction is committed, PostgreSQL will ensure either that all updates are done or else that none of them are done. Transactions have the standard ACID (atomic, consistent, isolatable, and durable) property. @@ -141,11 +141,6 @@ NOTICE: BeginTransactionBlock and not in default state Notes - - The keyword TRANSACTION is just a cosmetic alternative to WORK. - Neither keyword need be specified. - - Refer to for further information @@ -190,7 +185,7 @@ BEGIN WORK; BEGIN - is a Postgres language extension. + is a PostgreSQL language extension. There is no explicit BEGIN command in SQL92; transaction initiation is always implicit and it terminates either @@ -204,6 +199,12 @@ BEGIN WORK; + + Incidentally, the BEGIN keyword is used for a different + purpose in embedded SQL. You are advised to be careful about the transaction + semantics when porting database applications. + + SQL92 also requires SERIALIZABLE to be the default transaction isolation level. diff --git a/doc/src/sgml/ref/commit.sgml b/doc/src/sgml/ref/commit.sgml index 03dfea23f5f..651e649205b 100644 --- a/doc/src/sgml/ref/commit.sgml +++ b/doc/src/sgml/ref/commit.sgml @@ -1,5 +1,5 @@ @@ -62,7 +62,7 @@ COMMIT [ WORK | TRANSACTION ] -END +COMMIT @@ -72,7 +72,7 @@ END -NOTICE EndTransactionBlock and not inprogress/abort state +NOTICE: COMMIT: no transaction in progress @@ -141,7 +141,8 @@ COMMIT WORK; SQL92 - Full compatibility. + SQL92 only specifies the two forms COMMIT + and COMMIT WORK. Otherwise full compatibility. diff --git a/doc/src/sgml/ref/end.sgml b/doc/src/sgml/ref/end.sgml index 0b5f6ce41b9..5292b97e978 100644 --- a/doc/src/sgml/ref/end.sgml +++ b/doc/src/sgml/ref/end.sgml @@ -1,5 +1,5 @@ @@ -62,7 +62,7 @@ END [ WORK | TRANSACTION ] -END +COMMIT @@ -72,7 +72,7 @@ END -NOTICE EndTransactionBlock and not inprogress/abort state +NOTICE: COMMIT: no transaction in progress @@ -94,7 +94,7 @@ NOTICE EndTransactionBlock and not inprogress/abort state - END is a Postgres + END is a PostgreSQL synonym for . @@ -144,7 +144,7 @@ END WORK; - END is a Postgres + END is a PostgreSQL extension which provides functionality equivalent to . diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 4e3d5554801..8f68d7ede49 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1,5 +1,5 @@ @@ -1944,7 +1944,7 @@ testdb=> \set content `sed -e "s/'/\\\\\\'/g" < my_file.txt` %# - If the username is postgres, a + If the current user is a database superuser, then a #, otherwise a >. @@ -2020,10 +2020,9 @@ testdb=> \set content `sed -e "s/'/\\\\\\'/g" < my_file.txt` Before starting up in interactive mode, psql attempts - to read and execute the files /etc/psqlrc and - $HOME/.psqlrc. They could be used to set up the client or - the server to taste (using the \set and SET - commands). + to read and execute commands from the file $HOME/.psqlrc. It + could be used to set up the client or the server to taste (using the \set + and SET commands). @@ -2034,7 +2033,7 @@ testdb=> \set content `sed -e "s/'/\\\\\\'/g" < my_file.txt` psql supports the readline and history libraries for convenienent line editing and retrieval. The command history is stored in a file - named .psqlrc in your home directory and is reloaded when + named .psql_history in your home directory and is reloaded when psql starts up. Tab-completion is also supported, although the completion logic makes no claim to be an SQL parser. @@ -2088,7 +2087,7 @@ $ ./configure --with-includes=/opt/gnu/include --with-libraries=/opt/gnu/lib .. Notice the changing prompt. testdb=> CREATE TABLE my_table ( -testdb-> first int4 not null default 0, +testdb-> first integer not null default 0, testdb-> second text testdb-> ); CREATE @@ -2096,11 +2095,11 @@ CREATE Now look at the table definition again: testdb=> \d my_table - Table "my_table" - Attribute | Type | Info ------------+------+-------------------- - first | int4 | not null default 0 - second | text | + Table "my_table" + Attribute | Type | Modifier +-----------+---------+-------------------- + first | integer | not null default 0 + second | text | At this point you decide to change the prompt to something more diff --git a/doc/src/sgml/ref/rollback.sgml b/doc/src/sgml/ref/rollback.sgml index 17832debd16..dcee1df8bb2 100644 --- a/doc/src/sgml/ref/rollback.sgml +++ b/doc/src/sgml/ref/rollback.sgml @@ -1,5 +1,5 @@ @@ -61,8 +61,7 @@ ABORT -NOTICE: UserAbortTransactionBlock and not in in-progress state -ABORT +NOTICE: ROLLBACK: no transaction in progress @@ -95,10 +94,6 @@ ABORT Notes - - The keywords WORK and TRANSACTION are noise and can be omitted. - - Use to successfully terminate a transaction. @@ -134,8 +129,8 @@ ROLLBACK WORK; SQL92 - Full compatibility. The TRANSACTION keyword is a - Postgres extension. + SQL92 only specifies the two forms ROLLBACK + and ROLLBACK WORK. Otherwise full compatibility. diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 8808f9e799d..7c33e31e37f 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -1,5 +1,5 @@ @@ -23,7 +23,7 @@ Postgres documentation 1999-07-20 -UPDATE table SET R">colle> = expression [, ...] +UPDATE table SET col = expression [, ...] [ FROM fromlist ] [ WHERE condition ] -- cgit v1.2.3