diff options
| -rw-r--r-- | doc/src/sgml/ref/commands.sgml | 33 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_user.sgml | 321 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_view.sgml | 285 | ||||
| -rw-r--r-- | doc/src/sgml/ref/current_date.sgml | 171 | ||||
| -rw-r--r-- | doc/src/sgml/ref/current_time.sgml | 186 | ||||
| -rw-r--r-- | doc/src/sgml/ref/current_timestamp.sgml | 194 | ||||
| -rw-r--r-- | doc/src/sgml/ref/current_user.sgml | 193 | ||||
| -rw-r--r-- | doc/src/sgml/ref/declare.sgml | 270 | ||||
| -rw-r--r-- | doc/src/sgml/ref/delete.sgml | 207 | ||||
| -rw-r--r-- | doc/src/sgml/ref/reset.sgml | 152 | ||||
| -rw-r--r-- | doc/src/sgml/ref/revoke.sgml | 349 | ||||
| -rw-r--r-- | doc/src/sgml/ref/rollback.sgml | 143 | ||||
| -rw-r--r-- | doc/src/sgml/ref/select.sgml | 6 | ||||
| -rw-r--r-- | doc/src/sgml/ref/set.sgml | 581 | ||||
| -rw-r--r-- | doc/src/sgml/ref/show.sgml | 150 | ||||
| -rw-r--r-- | doc/src/sgml/ref/update.sgml | 190 |
16 files changed, 3423 insertions, 8 deletions
diff --git a/doc/src/sgml/ref/commands.sgml b/doc/src/sgml/ref/commands.sgml index 173963a08d8..d151fe02a7c 100644 --- a/doc/src/sgml/ref/commands.sgml +++ b/doc/src/sgml/ref/commands.sgml @@ -1,8 +1,8 @@ -<Chapter Id="commands"> -<Title>Commands</Title> +<chapter Id="sql-commands"> +<title>Commands</title> -<Para> -</Para> +<para> +</para> &alterTable; &alterUser; @@ -22,10 +22,33 @@ &createTable; &createTrigger; &createType; +&createUser; +&createView; +&declare; +&delete; &dropFunction; +&reset; +&revoke; +&rollback; &select; +&set; +&show; +&update; -</Chapter> +</chapter> + +<chapter Id="sql-functions"> +<title>SQL Functions</title> + +<para> +</para> + +¤tDate; +¤tTime; +¤tTimestamp; +¤tUser; + +</chapter> <!-- Keep this comment at the end of the file Local variables: diff --git a/doc/src/sgml/ref/create_user.sgml b/doc/src/sgml/ref/create_user.sgml new file mode 100644 index 00000000000..f0b807d195f --- /dev/null +++ b/doc/src/sgml/ref/create_user.sgml @@ -0,0 +1,321 @@ +<REFENTRY ID="SQL-CREATEUSER-1"> + <REFMETA> + <REFENTRYTITLE> + CREATE USER + </REFENTRYTITLE> + <REFMISCINFO>SQL - Language Statements</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + CREATE USER + </REFNAME> + <REFPURPOSE> + Creates account information for a new user + </REFPURPOSE> + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + + CREATE USER<REPLACEABLE CLASS="PARAMETER"> username</REPLACEABLE> + [WITH PASSWORD <REPLACEABLE CLASS="PARAMETER">password</REPLACEABLE>] + [CREATEDB | NOCREATEDB] + [CREATEUSER | NOCREATEUSER] + [IN GROUP <REPLACEABLE CLASS="PARAMETER">groupname</REPLACEABLE> [, ...] ] + [VALID UNTIL '<REPLACEABLE CLASS="PARAMETER">abstime</REPLACEABLE>'] + + + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-CREATEUSER-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">username</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The name of the user. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">password</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The WITH PASSWORD clause sets the user's password within + the "<filename>pg_shadow</filename>" table. For this reason, + <filename>"pg_shadow</filename>" is no + longer accessible to the instance of PostgreSQL that the + PostgreSQL user's password is initially set to NULL. + <comment>The text here has got garbled.</comment> + When a + user's password in the "<filename>pg_shadow</filename>" + table is NULL, user + authentication proceeds as it historically has (HBA, + PG_PASSWORD, etc). However, if a password is set for a + user, a new authentication system supplants any other + configured for the PostgreSQL instance, and the password + stored in the "<filename>pg_shadow</filename>" table is used + for authentication. + For more details on how this authentication system + functions see pg_crypt(3). If the WITH PASSWORD clause is + omitted, the user's password is set to the empty + string with equates to a NULL value in the authentication + system mentioned above. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue> CREATEDB/NOCREATEDB</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + These clauses define a user's ability to create databases. + If CREATEDB is specified, the user being defined will + be allowed to create his own databases. Using NOCREATEDB + will deny a user the ability to create databases. If this + clause is omitted, NOCREATEDB is used by default. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue>CREATEUSER/NOCREATEUSER</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + These clauses determine whether a user will be permitted to + create new + users in an instance of PostgreSQL. + Omitting this clause will set the user's value of this + attribute to be NOCREATEUSER. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">groupname</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + A name of a group into which to insert the user as a new member. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">abstime</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The VALID UNTIL clause sets an absolute time after which the + user's PostgreSQL login is no longer valid. Please note that + if a user does not have a password defined in the + "<filename>pg_shadow</filename>" + table, the valid until date will not be checked + during user authentication. If this clause is omitted, + a NULL value is stored in "<filename>pg_shadow</filename>" + for this attribute, + and the login will be valid for all time. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CREATEUSER-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>CREATE USER</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Message returned if the command completes successfully. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue>ERROR: removeUser: user "<replaceable class="parameter">username</replaceable>" does not exist</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + if "<replaceable class="parameter">username</replaceable>" not found. + </PARA> + <comment>I don't understand this and I don't know how to get +this error message.</comment> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CREATEUSER-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + CREATE USER will add a new user to an instance of PostgreSQL. + </PARA> + <PARA> + The new user will be given a <filename>usesysid</filename> of: + '<command>SELECT MAX(usesysid) + 1 FROM pg_shadow</command>'. + This means that + PostgreSQL users' <filename>usesysid</filename>s will not + correspond to their operating + system(OS) user ids. The exception to this rule is + the '<literal>postgres</literal>' user, whose OS user id + is used as the + <filename>usesysid</filename> during the initdb process. + If you still want the + OS user id and the <filename>usesysid</filename> to match + for any given user, + use the "createuser" script provided with the PostgreSQL + distribution. + </PARA> + + <REFSECT2 ID="R2-SQL-CREATEUSER-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + CREATE USER statement is a PostgreSQL language extension. + </PARA> + <para> + Use DROP USER or ALTER USER statements to remove or modify a user + account.</para> + <para> + Refer to the <filename>pg_shadow</filename> table for further information. + </para> + <programlisting> + Table = pg_shadow + +--------------------------+--------------------------+-------+ + | Field | Type | Length| + +--------------------------+--------------------------+-------+ + | usename | name | 32 | + | usesysid | int4 | 4 | + | usecreatedb | bool | 1 | + | usetrace | bool | 1 | + | usesuper | bool | 1 | + | usecatupd | bool | 1 | + | passwd | text | var | + | valuntil | abstime | 4 | + +--------------------------+--------------------------+-------+ + </programlisting> + </REFSECT2> + + <REFSECT1 ID="R1-SQL-CREATEUSER-2"> + <TITLE> + Usage + </TITLE> + <PARA> + Create a user with no password: + </PARA> + <ProgramListing> + CREATE USER jonathan + </ProgramListing> + <PARA> + Create a user with a password: + </PARA> + <ProgramListing> + CREATE USER davide WITH PASSWORD jw8s0F4 + </ProgramListing> + <para> + Create a user with a password, whose account is valid until the end of 2001. + Note that after one second has ticked in 2002, the account is not + valid: + </para> + <ProgramListing> + CREATE USER miriam WITH PASSWORD jw8s0F4 VALID UNTIL 'Jan 1 2002' + </ProgramListing> + <para> + Create an account where the user can create databases: + </para> + <ProgramListing> + CREATE USER manuel WITH PASSWORD jw8s0F4 CREATEDB + </ProgramListing> + + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CREATEUSER-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-CREATEUSER-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + There is no CREATE USER statement in SQL92. + </PARA> +</REFENTRY> + + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +-->
\ No newline at end of file diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml new file mode 100644 index 00000000000..a626e7e0981 --- /dev/null +++ b/doc/src/sgml/ref/create_view.sgml @@ -0,0 +1,285 @@ +<REFENTRY ID="SQL-CREATEVIEW-1"> + <REFMETA> + <REFENTRYTITLE> + CREATE VIEW + </REFENTRYTITLE> + <REFMISCINFO>SQL - Language Statements</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + CREATE VIEW + </REFNAME> + <REFPURPOSE> +Constructs a virtual table + </REFPURPOSE> + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + CREATE VIEW <REPLACEABLE CLASS="PARAMETER">view</REPLACEABLE> + AS SELECT <REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE> + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-CREATEVIEW-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">view</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The name of a view to be created. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">query</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> +An SQL query which will provide the columns and rows of the view. + </PARA> + <PARA> + Refer to the SELECT statement for more information + about valid arguments. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CREATEVIEW-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>CREATE</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The message returned if the view is successfully created. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue> + WARN amcreate: "<replaceable class="parameter">view</replaceable>" relation already exists + </ReturnValue> + </TERM> + <LISTITEM> + <PARA> + This error occurs if the view specified already exists in the database. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue>NOTICE create: attribute named "<replaceable class="parameter">column</replaceable>" has an unknown type</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The view will be created having a column with an unknown type + if you do not specify it. For example, the following command gives + an error: + <programlisting> + CREATE VIEW vista AS SELECT 'Hello World' + </programlisting> + whereas this command does not: + <programlisting> + CREATE VIEW vista AS SELECT 'Hello World'::text + </programlisting> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CREATEVIEW-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + CREATE VIEW will define a view of a table. This view is + not physically materialized. Specifically, a query + rewrite retrieve rule is automatically generated + to support retrieve operations on views. + </PARA> + + <REFSECT2 ID="R2-SQL-CREATEVIEW-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + Use the DROP VIEW statement to drop views. + </PARA> + </refsect2> + + <REFSECT2 ID="R2-SQL-CREATEVIEW-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Bugs + </TITLE> + <para> + Currently, views are read only. + </para> + </REFSECT2> + + <REFSECT1 ID="R1-SQL-CREATEVIEW-2"> + <TITLE> + Usage + </TITLE> + <PARA> + Create a view consisting of all Comedy films: + </PARA> + <ProgramListing> + CREATE VIEW kinds AS + SELECT * + FROM films + WHERE kind = 'Comedy'; + + SELECT * FROM kinds; + + code |title |did| date_prod|kind |len + -----+-------------------------+---+----------+----------+------ + UA502|Bananas |105|1971-07-13|Comedy | 01:22 + C_701|There's a Girl in my Soup|107|1970-06-11|Comedy | 01:36 + </ProgramListing> + + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CREATEVIEW-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-CREATEVIEW-5"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + SQL92 specifies some additional capabilities for the CREATE VIEW statement: + </PARA> + <programlisting> + CREATE VIEW <replaceable class="parameter">view</replaceable> [ <replaceable class="parameter">column</replaceable> [, ...] ] + AS SELECT <replaceable class="parameter">expression</replaceable> [AS <replaceable class="parameter">colname</replaceable>] [, ...] + FROM <replaceable class="parameter">table</replaceable> + [WHERE <replaceable class="parameter">condition</replaceable>] + [ WITH [ CASCADE | LOCAL ] CHECK OPTION ] + </programlisting> + + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>CHECK OPTION</ReturnValue> + </TERM> + <LISTITEM> + <PARA> This option is to do with updatable views. + All INSERTs and UPDATEs on the view will be + checked to ensure data satisfy the view-defining + condition. If they do not, the update will be rejected. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue>LOCAL</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Check for integrity on this view. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue>CASCADE</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Check for integrity on this view and on any dependent + view. CASCADE is assumed if neither CASCADE nor LOCAL is specified. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect2> + </refsect1> +</REFENTRY> + + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +-->
\ No newline at end of file diff --git a/doc/src/sgml/ref/current_date.sgml b/doc/src/sgml/ref/current_date.sgml new file mode 100644 index 00000000000..034e23c8b47 --- /dev/null +++ b/doc/src/sgml/ref/current_date.sgml @@ -0,0 +1,171 @@ +<REFENTRY ID="SQL-CURRENT-DATE-1"> + <REFMETA> + <REFENTRYTITLE> + SQL_CURRENT_DATE + </REFENTRYTITLE> + <REFMISCINFO>SQL - Functions</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + SQL_CURRENT_DATE + </REFNAME> + <REFPURPOSE> +- returns the current date, (i.e "today"). + </REFPURPOSE> + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + CURRENT_DATE + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-CURRENT-DATE-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> +<VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>None</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CURRENT-DATE-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>Returns "<replaceable class="parameter">today</replaceable>"</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CURRENT-DATE-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + The niladic CURRENT_DATE function has a data type of + DATE and returns the date at the time that it is run. + </PARA> + + <REFSECT2 ID="R2-SQL-CURRENT-DATE-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + Refer to SET DATESTYLE for further information about date format. + </PARA> + </REFSECT2> + + <REFSECT1 ID="R1-SQL-CURRENT-DATE-2"> + <TITLE> + Usage + </TITLE> + <PARA> + Insert the date of insertion into a row: + </PARA> + <ProgramListing> + INSERT INTO films + VALUES ('TM999','Ben Hur',105,CURRENT_DATE,'Action',NULL); + </ProgramListing> + + <PARA> + Display CURRENT-DATE: + </PARA> + <ProgramListing> + SELECT CURRENT_DATE AS today; + + today + ------------ + 1998-03-31 + + </ProgramListing> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CURRENT-DATE-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-CURRENT-DATE-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + Full compatibility. + </PARA> + </refsect2> + </refsect1> +</REFENTRY> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +-->
\ No newline at end of file diff --git a/doc/src/sgml/ref/current_time.sgml b/doc/src/sgml/ref/current_time.sgml new file mode 100644 index 00000000000..a4d56342979 --- /dev/null +++ b/doc/src/sgml/ref/current_time.sgml @@ -0,0 +1,186 @@ +<REFENTRY ID="SQL-CURRENT-TIME-1"> + <REFMETA> + <REFENTRYTITLE> + SQL_CURRENT_TIME + </REFENTRYTITLE> + <REFMISCINFO>SQL - Functions</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + SQL_CURRENT_TIME + </REFNAME> + <REFPURPOSE> + - returns the current local time, (i.e "now"). + </REFPURPOSE> + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + CURRENT_TIME + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-CURRENT-TIME-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>None</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CURRENT-TIME-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>Returns "<replaceable class="parameter">now</replaceable>"</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CURRENT-TIME-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + The niladic CURRENT_TIME function has a data type of + TIME and returns the local time when it is run. + </PARA> + + <REFSECT2 ID="R2-SQL-CURRENT-TIME-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> Refer to the SET TIME ZONE statement for a further description + of local time. + </PARA> + </REFSECT2> + + <REFSECT1 ID="R1-SQL-CURRENT-TIME-2"> + <TITLE> + Usage + </TITLE> + <PARA>Display CURRENT_TIME: + </PARA> + <ProgramListing> + SELECT CURRENT_TIME AS now; + + now + ----------- + 17:41:31+02 + + </ProgramListing> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CURRENT-TIME-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-CURRENT-TIME-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + SQL92 specifies some additional capabilities for CURRENT_TIME: + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue> + CURRENT_TIME [ (<replaceable class="parameter">scale</replaceable>) ]</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The optional scale for CURRENT_TIME, if specified, is an + unsigned integer representing the number of digits in the + optional seconds fraction of the time value represented + by the function. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> +</REFENTRY> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +-->
\ No newline at end of file diff --git a/doc/src/sgml/ref/current_timestamp.sgml b/doc/src/sgml/ref/current_timestamp.sgml new file mode 100644 index 00000000000..1a170efcb02 --- /dev/null +++ b/doc/src/sgml/ref/current_timestamp.sgml @@ -0,0 +1,194 @@ +<REFENTRY ID="SQL-CURRENT-TIMESTAMP-1"> + <REFMETA> + <REFENTRYTITLE> + SQL_CURRENT_TIMESTAMP + </REFENTRYTITLE> + <REFMISCINFO>SQL - Functions</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + SQL_CURRENT_TIMESTAMP + </REFNAME> + <REFPURPOSE> + returns the current timestamp, (i.e the + date "today" concatenated with time "now"). + </REFPURPOSE> + + + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <synopsis> + CURRENT_TIMESTAMP + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-CURRENT-TIMESTAMP-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>None</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CURRENT-TIMESTAMP-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>Returns "<replaceable class="parameter">today</replaceable>" and "<replaceable class="parameter">now</replaceable>".</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CURRENT-TIMESTAMP-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + The niladic CURRENT_TIMESTAMP function has a data type of + TIMESTAMP and returns the date and local time at which it is run. + </PARA> + + <REFSECT2 ID="R2-SQL-CURRENT-TIMESTAMP-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + Refer to the SET TIME ZONE statement for a further description + of local time. + </PARA> + + + </REFSECT2> + + <REFSECT1 ID="R1-SQL-CURRENT-TIMESTAMP-2"> + <TITLE> + Usage + </TITLE> + <PARA> + Display CURRENT_TIMESTAMP: + </PARA> + <ProgramListing> + SELECT CURRENT_TIMESTAMP AS date_and_time; + + date_and_time + ---------------------- + 1998-03-31 07:41:21-08 + </ProgramListing> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CURRENT-TIMESTAMP-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-CURRENT-TIMESTAMP-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + SQL92 specifies some additional capabilities for CURRENT_TIMESTAMP: + </PARA> + + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>CURRENT_TIMESTAMP [ (<replaceable class="parameter">scale</replaceable>) ]</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The optional scale for CURRENT_TIMESTAMP, if specified, is an + unsigned integer representing the number of digits in the + optional seconds fraction of the time value represented + by the function. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </para> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </refsect2> + </refsect1> +</REFENTRY> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +-->
\ No newline at end of file diff --git a/doc/src/sgml/ref/current_user.sgml b/doc/src/sgml/ref/current_user.sgml new file mode 100644 index 00000000000..4f619ebbf08 --- /dev/null +++ b/doc/src/sgml/ref/current_user.sgml @@ -0,0 +1,193 @@ +<REFENTRY ID="SQL-CURRENT-USER-1"> + <REFMETA> + <REFENTRYTITLE> + SQL_CURRENT_USER + </REFENTRYTITLE> + <REFMISCINFO>SQL - Functions</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + SQL_CURRENT_USER + </REFNAME> + <REFPURPOSE> + - returns the current user name. + </REFPURPOSE> + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + CURRENT_USER + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-CURRENT-USER-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>None</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CURRENT-USER-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>The name of the current user.</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CURRENT-USER-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + The niladic CURRENT_USER function returns a string of type "name" + whose value represents a user name identification. + </PARA> + + <REFSECT2 ID="R2-SQL-CURRENT-USER-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + Data type "name" is a non-standard 31-character type for storing + system identifiers. + </PARA> + </REFSECT2> + + <REFSECT1 ID="R1-SQL-CURRENT-USER-2"> + <TITLE> + Usage + </TITLE> + <PARA> + Display CURRENT_USER + </PARA> + <ProgramListing> + SELECT CURRENT_USER AS who_am_i; + + who_am_i + ------------ + jose + </ProgramListing> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-CURRENT-USER-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-CURRENT-USER-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + SQL92 specifies some additional niladic USER functions: + </PARA> + <variablelist> + <varlistentry> + <term>CURRENT_USER / USER</term> + <listitem> + <para> + USER is a synonym for CURRENT_USER. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>SESSION_USER</term> + <listitem> + <para> + The SESSION_USER function returns the SQL-session user name. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>SYSTEM_USER</term> + <listitem> + <para> + The SYSTEM_USER function returns the database's initial default user. + </para> + </listitem> + </varlistentry> + </variablelist> + <para> + A niladic USER function returns a SQL_TEXT character string whose + value represents a user name. + </para> + </refsect2> + </refsect1> +</REFENTRY> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +-->
\ No newline at end of file diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml new file mode 100644 index 00000000000..b4a16ff5f54 --- /dev/null +++ b/doc/src/sgml/ref/declare.sgml @@ -0,0 +1,270 @@ +<REFENTRY ID="SQL-DECLARESTATEMENT-1"> + <REFMETA> + <REFENTRYTITLE> + DECLARE STATEMENT + </REFENTRYTITLE> + <REFMISCINFO>SQL - Language Statements</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + DECLARE STATEMENT + </REFNAME> + <REFPURPOSE> + Declares a cursor + </REFPURPOSE> + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + DECLARE <replaceable class="parameter">cursor</replaceable> [ BINARY ] + FOR SELECT <replaceable class="parameter">query</replaceable> + </SYNOPSIS> + <REFSECT2 ID="R2-SQL-DECLARESTATEMENT-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>BINARY</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The BINARY keyword causes the cursor to fetch data in binary + rather than in ASCII format. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">cursor</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The cursor's name. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">query</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + An SQL query which will provide the rows to be governed by the + cursor. + </PARA> + <PARA> + Refer to the SELECT statement for further information about + valid arguments. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-DECLARESTATEMENT-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>SELECT</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The message returned if the SELECT is run successfully. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue>NOTICE + BlankPortalAssignName: portal "<replaceable class="parameter">cursor</replaceable>" already exists</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + This error occurs if cursor "<replaceable class="parameter">cursor</replaceable>" is already declared. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-DECLARESTATEMENT-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + DECLARE allows a user to create cursors, which can be used to retrieve + a small number of rows at a time out of a larger query. Cursors can return + data either in ASCII or in binary foramt. + </PARA> + <PARA> + Normal cursors return data in ASCII format. Since + data is stored natively in binary format, the system must + do a conversion to produce the ASCII format. In addition, + ASCII formats are often larger in size than binary format. + Once the information comes back in ASCII, the client + application often has to convert it to a binary format to + manipulate it anyway. + </PARA> + <PARA> + BINARY cursors give you back the data in the native binary + representation. So binary cursors will tend to be a + little faster since they suffer less conversion overhead. + For example, for an integer column, you get a C integer number like ^A + using a binary cursor, while you get a string value like '1' + using the non binary cursor. + </PARA> + <PARA> + However, ASCII is architecture-neutral whereas binary + representation can differ between different machine architectures. + Therefore, if your client machine and server machine use different + representations, you will probably not want your data returned in + binary format. + Again, if you intend to display the data in + ASCII, getting it back in ASCII will save you some + effort on the client side. + </PARA> + + <REFSECT2 ID="R2-SQL-DECLARESTATEMENT-3"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + Cursors are only available in transactions. + </PARA> + <PARA> + PostgreSQL does not have an explicit <command>OPEN cursor</command> + statement; a cursor is considered to be open when it is DECLAREd. + </PARA> + </REFSECT2> + </refsect1> + + <REFSECT1 ID="R1-SQL-DECLARESTATEMENT-2"> + <TITLE> + Usage + </TITLE> + <PARA> + To declare a cursor: + </PARA> + <ProgramListing> + DECLARE liahona CURSOR + FOR SELECT * FROM films; + </ProgramListing> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-DECLARESTATEMENT-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-DECLARESTATEMENT-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + SQL92 specifies some additional capabilities for the DECLARE statement: + </PARA> + <synopsis> + DECLARE cursor [ INSENSITIVE ] [ SCROLL ] CURSOR + FOR SELECT expression + [ ORDER BY column [, ... ] [ ASC | DESC ] + [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ] + </synopsis> + <variablelist> + <varlistentry> + <term></term> + <listitem> + <variablelist> + <varlistentry> + <term>INSENSITIVE</term> + <listitem> + <para> + UPDATE and DELETE CURRENT operations are not allowed + if the cursor is declared to be INSENSITIVE. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>SCROLL</term> + <listitem> + <para> + If SCROLL is not specified, only FETCH NEXT will be allowed. + </para> + </listitem> + </varlistentry> + <varlistentry> + <term>FOR READ ONLY/UPDATE</term> + <listitem> + <para> + If READ ONLY is specified, UPDATE/DELETE CURRENT operations + will not be allowed. + </para> + </listitem> + </varlistentry> + </variablelist> + </listitem> + </varlistentry> + </variablelist> +</REFENTRY> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +--> diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml new file mode 100644 index 00000000000..7b1d6fc89e3 --- /dev/null +++ b/doc/src/sgml/ref/delete.sgml @@ -0,0 +1,207 @@ +<REFENTRY ID="SQL-DELETE-1"> + <REFMETA> + <REFENTRYTITLE> + DELETE + </REFENTRYTITLE> + <REFMISCINFO>SQL - Language Statements</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + DELETE + </REFNAME> + <REFPURPOSE> + Deletes rows from a table + </REFPURPOSE> + + + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + DELETE FROM <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> [ WHERE <REPLACEABLE CLASS="PARAMETER">condition</REPLACEABLE> ] + </SYNOPSIS> + + <REFSECT2 ID="R2-SQL-DELETE-1"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">table</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + The name of an existing table. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <ReturnValue><replaceable class="parameter">condition</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + This is an SQL selection query which returns the rows which + are to be deleted. + </PARA> + <PARA> + Refer to the SELECT statement for further description + of the WHERE clause. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-DELETE-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue> DELETE <replaceable class="parameter">count</replaceable></ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Message returned if items are successfully deleted. The + <replaceable class="parameter">count</replaceable> is the number + of rows deleted. + </PARA> + <PARA> + If <replaceable class="parameter">count</replaceable> is 0, + no rows are deleted. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> +</REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-DELETE-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + DELETE removes rows which satisfy the WHERE <replaceable class="parameter">condition</replaceable>, + from the specified table. + </PARA> + <PARA> + If the <replaceable class="parameter">condition</replaceable> is absent, + the effect is to delete all rows in the table. + The result is a valid, but empty table. + </PARA> + <PARA> + You must have write access to the table in order to modify + it, as well as read access to any table whose values are + read in the <replaceable class="parameter">condition</replaceable>. + </PARA> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-DELETE-2"> + <TITLE> + Usage + </TITLE> + <PARA> + Remove all films but musicals: + </PARA> + <ProgramListing> + DELETE FROM films WHERE kind <> 'Musical'; + + SELECT * FROM films; + + code |title |did| date_prod|kind |len + -----+-------------------------+---+----------+----------+------ + UA501|West Side Story |105|1961-01-03|Musical | 02:32 + TC901|The King and I |109|1956-08-11|Musical | 02:13 + WD101|Bed Knobs and Broomsticks|111| |Musical | 01:57 + </ProgramListing> + <para> + Clear the table films: + </para> + <programlisting> + DELETE FROM films; + + SELECT * FROM films; + code|title|did|date_prod|kind|len + ----+-----+---+---------+----+--- + (0 rows) + </programlisting> + </REFSECT1> + + <REFSECT1 ID="R1-SQL-DELETE-3"> + <TITLE> + Compatibility + </TITLE> + <PARA> + </PARA> + + <REFSECT2 ID="R2-SQL-DELETE-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + SQL92 defines a different syntax for a positioned DELETE statement: + </PARA> + + <synopsis> + DELETE FROM <replaceable class="parameter">table</replaceable> WHERE CURRENT OF <replaceable class="parameter">cursor</replaceable> + </synopsis> + <para> + where <replaceable class="parameter">cursor</replaceable> identifies an open cursor.</para> + </refsect2> + </refsect1> +</REFENTRY> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +sgml-shorttag:t +sgml-minimize-attributes:nil +sgml-always-quote-attributes:t +sgml-indent-step:1 +sgml-indent-data:t +sgml-parent-document:nil +sgml-default-dtd-file:"../reference.ced" +sgml-exposed-tags:nil +sgml-local-catalogs:"/usr/lib/sgml/catalog" +sgml-local-ecat-files:nil +End: +-->
\ No newline at end of file diff --git a/doc/src/sgml/ref/reset.sgml b/doc/src/sgml/ref/reset.sgml new file mode 100644 index 00000000000..39829f631d8 --- /dev/null +++ b/doc/src/sgml/ref/reset.sgml @@ -0,0 +1,152 @@ +<REFENTRY ID="SQL-RESET"> +<REFMETA> +<REFENTRYTITLE> +RESET +</REFENTRYTITLE> +<REFMISCINFO>SQL - Language Statements</REFMISCINFO> +</REFMETA> +<REFNAMEDIV> +<REFNAME> +RESET +</REFNAME> +<REFPURPOSE> +Restores run-time parameters for session to default values +</REFPURPOSE> + +<REFSYNOPSISDIV> +<REFSYNOPSISDIVINFO> +<DATE>1998-09-01</DATE> +</REFSYNOPSISDIVINFO> +<SYNOPSIS> +RESET <REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> +</SYNOPSIS> + +<REFSECT2 ID="R2-SQL-RESET-1"> +<REFSECT2INFO> +<DATE>1998-09-01</DATE> +</REFSECT2INFO> +<TITLE> +Inputs +</TITLE> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> +</TERM> +<LISTITEM> +<PARA> + Refer to the SET statement for more information on available + variables. +</variablelist> + +</REFSECT2> + +<REFSECT2 ID="R2-SQL-RESET-2"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +Outputs +</TITLE> +<PARA> +</PARA> + +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +RESET VARIABLE +</TERM> +<LISTITEM> +<PARA> + Message returned if successfully. + +</VARIABLELIST> + +</REFSECT2> +</REFSYNOPSISDIV> + +<REFSECT1 ID="R1-SQL-RESET-1"> +<REFSECT1INFO> +<DATE>1998-04-15</DATE> +</REFSECT1INFO> +<TITLE> +Description +</TITLE> +<PARA> + The RESET statement restores variables to the default values. +Refer to the SET command for details on allowed values and defaults. +RESET is an alternate form for +<synopsis> +<command>SET <replaceable class="parameter">variable</replaceable> = DEFAULT</command> +</synopsis> + +<REFSECT2 ID="R2-SQL-RESET-3"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +Notes +</TITLE> +<PARA> +The RESET statement is a <productname>Postgres</productname> language extension. +<para> + Refer to SET/SHOW statements to set/show variable values. + +</REFSECT2> +</refsect1> + +<REFSECT1 ID="R1-SQL-RESET-2"> +<TITLE> +Usage +</TITLE> +<PARA> +<ProgramListing> + -- reset DateStyle to its default; + RESET DateStyle; +</programlisting> +<programlisting> + -- reset Geqo to its default; + RESET GEQO; +</ProgramListing> + +</REFSECT1> + +<REFSECT1 ID="R1-SQL-RESET-3"> +<TITLE> +Compatibility +</TITLE> +<PARA> +</PARA> + +<REFSECT2 ID="R2-SQL-RESET-4"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +SQL92 +</TITLE> +<PARA> + There is no RESET statement in SQL92. +</REFENTRY> + +<!-- +<REPLACEABLE CLASS="PARAMETER"> +</REPLACEABLE> +<ReturnValue></ReturnValue> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM>• +</TERM> +<LISTITEM> +<PARA> +</PARA> +</LISTITEM> +</VARLISTENTRY> +</VARIABLELIST> +<PARA> +</PARA> +--> diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml new file mode 100644 index 00000000000..5493e93a283 --- /dev/null +++ b/doc/src/sgml/ref/revoke.sgml @@ -0,0 +1,349 @@ +<REFENTRY ID="SQL-REVOKE"> +<REFMETA> +<REFENTRYTITLE> +REVOKE +</REFENTRYTITLE> +<REFMISCINFO>SQL - Language Statements</REFMISCINFO> +</REFMETA> +<REFNAMEDIV> +<REFNAME> +REVOKE +</REFNAME> +<REFPURPOSE> +Revokes access privilege from a user, a group or all users. +</REFPURPOSE> + +<REFSYNOPSISDIV> +<REFSYNOPSISDIVINFO> +<DATE>1998-09-01</DATE> +</REFSYNOPSISDIVINFO> +<SYNOPSIS> +<REPLACEABLE CLASS="PARAMETER"> +</REPLACEABLE> +REVOKE <REPLACEABLE CLASS="PARAMETER">privilege</REPLACEABLE> [, ...] + ON <REPLACEABLE CLASS="PARAMETER">object</REPLACEABLE> [, ...] + FROM { PUBLIC | GROUP <REPLACEABLE CLASS="PARAMETER">group</REPLACEABLE> | <REPLACEABLE CLASS="PARAMETER">username</REPLACEABLE> } +</SYNOPSIS> + +<REFSECT2 ID="R2-SQL-REVOKE-1"> +<REFSECT2INFO> +<DATE>1998-09-01</DATE> +</REFSECT2INFO> +<TITLE> +Inputs +</TITLE> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">privilege</REPLACEABLE> +</TERM> +<LISTITEM> +<PARA> + The possible privileges are: + +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +SELECT +</TERM> +<LISTITEM> +<PARA> +Privilege to access all of the columns of a specific + table/view. +</PARA> +</LISTITEM> +</VARLISTENTRY> + +<VARLISTENTRY> +<TERM> +INSERT +</TERM> +<LISTITEM> +<PARA> +Privilege to insert data into all columns of a + specific table. + +<VARLISTENTRY> +<TERM> +UPDATE +</TERM> +<LISTITEM> +<PARA> +Privilege to update all columns of a specific + table. + +<VARLISTENTRY> +<TERM> +DELETE +</TERM> +<LISTITEM> +<PARA> +Privilege to delete rows from a specific table. + +<VARLISTENTRY> +<TERM> +RULE +</TERM> +<LISTITEM> +<PARA> +Privilege to define rules on table/view. +(See the <command>CREATE RULE</command> statement). + +<VARLISTENTRY> +<TERM> +ALL +</TERM> +<LISTITEM> +<PARA> +Rescind all privileges. + +</VARIABLELIST> + +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">object</REPLACEABLE> +</TERM> +<LISTITEM> +<PARA> +The name of an object from which to revoke access. + The possible objects are: +<simplelist> +<member> + table +<member> + view +<member> + sequence +<member> + index +</simplelist> + +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">group</REPLACEABLE> +</TERM> +<LISTITEM> +<PARA> + The name of a group from whom to revoke privileges. +</PARA> +</LISTITEM> +</VARLISTENTRY> + +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">username</REPLACEABLE> +</TERM> +<LISTITEM> +<PARA> +The name of a user from whom revoke privileges. Use the PUBLIC keyword +to specify all users. +</PARA> +</LISTITEM> +</VARLISTENTRY> + +<VARLISTENTRY> +<TERM> +PUBLIC +</TERM> +<LISTITEM> +<PARA> +Rescind the specified privilege(s) for all users. + +</LISTITEM> +</VARLISTENTRY> +</VARIABLELIST> + +</REFSECT2> + +<REFSECT2 ID="R2-SQL-REVOKE-2"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +Outputs +</TITLE> +<PARA> +</PARA> + +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +CHANGE +</TERM> +<LISTITEM> +<PARA> + Message returned if successfully. + +<VARLISTENTRY> +<TERM> +ERROR +</TERM> +<LISTITEM> +<PARA> + Message returned if object is not available or impossible + to revoke privileges from a group or users. + +</VARIABLELIST> + +</REFSECT2> +</REFSYNOPSISDIV> + +<REFSECT1 ID="R1-SQL-REVOKE-1"> +<REFSECT1INFO> +<DATE>1998-09-01</DATE> +</REFSECT1INFO> +<TITLE> +Description +</TITLE> +<PARA> + REVOKE allows creator of an object to revoke permissions granted + before, from all users (via PUBLIC) or a certain user or group. + +<REFSECT2 ID="R2-SQL-REVOKE-3"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +Notes +</TITLE> +<PARA> + Refer to psql \z command for further information about permissions + on existing objects: + +<programlisting> + Database = lusitania + +------------------+---------------------------------------------+ + | Relation | Grant/Revoke Permissions | + +------------------+---------------------------------------------+ + | mytable | {"=rw","miriam=arwR","group todos=rw"} | + +------------------+---------------------------------------------+ + Legend: + uname=arwR -- privileges granted to a user + group gname=arwR -- privileges granted to a GROUP + =arwR -- privileges granted to PUBLIC + + r -- SELECT + w -- UPDATE/DELETE + a -- INSERT + R -- RULE + arwR -- ALL +</programlisting> + +<tip> +<para> +Currently, to create a GROUP you have to insert + data manually into table pg_group as: +<programlisting> + INSERT INTO pg_group VALUES ('todos'); + CREATE USER miriam IN GROUP todos; +</programlisting> +</tip> + +</REFSECT2> + +<REFSECT1 ID="R1-SQL-REVOKE-2"> +<TITLE> +Usage +</TITLE> +<PARA> +<ProgramListing> + -- revoke insert privilege from all users on table films: + -- + REVOKE INSERT ON films FROM PUBLIC; + + -- revoke all privileges from user manuel on view kinds: + -- + REVOKE ALL ON kinds FROM manuel; +</ProgramListing> + +</REFSECT1> + +<REFSECT1 ID="R1-SQL-REVOKE-3"> +<TITLE> +Compatibility +</TITLE> +<PARA> +</PARA> + +<REFSECT2 ID="R2-SQL-REVOKE-4"> +<REFSECT2INFO> +<DATE>1998-09-01</DATE> +</REFSECT2INFO> +<TITLE> +SQL92 +</TITLE> +<PARA> + The SQL92 syntax for REVOKE has additional capabilities for rescinding +privileges, including those on individual columns in tables: + +<variablelist> +<varlistentry> +<term> +<synopsis> +REVOKE { SELECT | DELETE | USAGE | ALL PRIVILEGES } [, ...] + ON <replaceable class="parameter">object</replaceable> + FROM { PUBLIC | <replaceable class="parameter">username</replaceable> [, ...] } { RESTRICT | CASCADE } +REVOKE { INSERT | UPDATE | REFERENCES } [, ...] [ ( <replaceable class="parameter">column</replaceable> [, ...] ) ] + ON <replaceable class="parameter">object</replaceable> + FROM { PUBLIC | <replaceable class="parameter">username</replaceable> [, ...] } { RESTRICT | CASCADE } +</synopsis> +<listitem> +<para> +Refer to the <command>GRANT</command> command for details on individual fields. + +<varlistentry> +<term> +<synopsis> +REVOKE GRANT OPTION FOR <replaceable class="parameter">privilege</replaceable> [, ...] + ON <replaceable class="parameter">object</replaceable> + FROM { PUBLIC | <replaceable class="parameter">username</replaceable> [, ...] } { RESTRICT | CASCADE } +</synopsis> +<listitem> +<para> +Rescinds authority for a user to grant the specified privilege to others. +Refer to the <command>GRANT</command> command for details on individual fields. + +</variablelist> + +<para> + The possible objects are: +<simplelist> +<member> [ TABLE ] table/view +<member> CHARACTER SET character-set +<member> COLLATION collation +<member> TRANSLATION translation +<member> DOMAIN domain +</simplelist> + +<para> +If user1 gives a privilege WITH GRANT OPTION to user2, + and user2 gives it to user3 then user1 can revoke + this privilege in cascade using the CASCADE keyword. + +If user1 gives a privilege WITH GRANT OPTION to user2, + and user2 gives it to user3 then if user1 try revoke + this privilege it fails if he/she specify the RESTRICT + keyword. +</REFENTRY> + +<!-- +<REPLACEABLE CLASS="PARAMETER"> +</REPLACEABLE> +<ReturnValue></ReturnValue> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM>• +</TERM> +<LISTITEM> +<PARA> +</PARA> +</LISTITEM> +</VARLISTENTRY> +</VARIABLELIST> +<PARA> +</PARA> +--> diff --git a/doc/src/sgml/ref/rollback.sgml b/doc/src/sgml/ref/rollback.sgml new file mode 100644 index 00000000000..7554cbe6c61 --- /dev/null +++ b/doc/src/sgml/ref/rollback.sgml @@ -0,0 +1,143 @@ +<REFENTRY ID="SQL-ROLLBACK"> +<REFMETA> +<REFENTRYTITLE> +ROLLBACK +</REFENTRYTITLE> +<REFMISCINFO>SQL - Language Statements</REFMISCINFO> +</REFMETA> +<REFNAMEDIV> +<REFNAME> +ROLLBACK +</REFNAME> +<REFPURPOSE> +Aborts the current transaction +</REFPURPOSE> +<REFSYNOPSISDIV> +<REFSYNOPSISDIVINFO> +<DATE>1998-09-01</DATE> +</REFSYNOPSISDIVINFO> +<SYNOPSIS> +ROLLBACK [ WORK ] +</SYNOPSIS> + +<REFSECT2 ID="R2-SQL-ROLLBACK-1"> +<REFSECT2INFO> +<DATE>1998-09-01</DATE> +</REFSECT2INFO> +<TITLE> +Inputs +</TITLE> +<PARA> +None. + +</REFSECT2> + +<REFSECT2 ID="R2-SQL-ROLLBACK-2"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +Outputs +</TITLE> +<PARA> + +<VARIABLELIST> +<VARLISTENTRY> +<TERM> + ABORT +</TERM> +<LISTITEM> +<PARA> + Message returned if successful. + +<VARLISTENTRY> +<TERM> +NOTICE: UserAbortTransactionBlock and not in in-progress state +ABORT +</TERM> +<LISTITEM> +<PARA> + If there is not any transaction currently in progress. + +</VARLISTENTRY> +</VARIABLELIST> + +</REFSECT2> +</REFSYNOPSISDIV> + +<REFSECT1 ID="R1-SQL-ROLLBACK-1"> +<REFSECT1INFO> +<DATE>1998-04-15</DATE> +</REFSECT1INFO> +<TITLE> +Description +</TITLE> +<PARA> + ROLLBACK rolls back the current transaction and causes + all the updates made by the transaction to be discarded. + +<REFSECT2 ID="R2-SQL-ROLLBACK-3"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +Notes +</TITLE> +<PARA> +The keyword WORK is noise and can be omitted. + +<para> +Use the COMMIT statement to successfully terminate a transaction. + +</REFSECT1> + +<REFSECT1 ID="R1-SQL-ROLLBACK-2"> +<TITLE> +Usage +</TITLE> +<PARA> +<ProgramListing> + --To abort all changes: + -- + ROLLBACK WORK; +</ProgramListing> + +</REFSECT1> + +<REFSECT1 ID="R1-SQL-ROLLBACK-3"> +<TITLE> +Compatibility +</TITLE> +<PARA> +</PARA> + +<REFSECT2 ID="R2-SQL-ROLLBACK-4"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +SQL92 +</TITLE> +<PARA> + Full compatibility. +</REFENTRY> + +<!-- +<REPLACEABLE CLASS="PARAMETER"> +</REPLACEABLE> +<ReturnValue></ReturnValue> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM>• +</TERM> +<LISTITEM> +<PARA> +</PARA> +</LISTITEM> +</VARLISTENTRY> +</VARIABLELIST> +<PARA> +</PARA> +--> diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 3f645127b20..1f4818b8b0f 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -188,6 +188,9 @@ The count of rows returned by the query. Description </TITLE> <PARA> +SELECT is the principal means to access information + in <productname>Postgres</productname>. + SELECT will get all rows which satisfy the WHERE condition or all rows of a table if WHERE is omitted. @@ -195,17 +198,14 @@ Description The GROUP BY clause allows a user to divide a table conceptually into groups. (See GROUP BY clause). -<PARA> The HAVING clause specifies a grouped table derived by the elimination of groups from the result of the previously specified clause. (See HAVING clause). -<PARA> The ORDER BY clause allows a user to specify that he/she wishes the rows sorted according to the ASCending or DESCending mode operator. (See ORDER BY clause) -<PARA> The UNION clause specifies a table derived from a Cartesian product union join. (See UNION clause). diff --git a/doc/src/sgml/ref/set.sgml b/doc/src/sgml/ref/set.sgml new file mode 100644 index 00000000000..87e9b7c6ffd --- /dev/null +++ b/doc/src/sgml/ref/set.sgml @@ -0,0 +1,581 @@ +<REFENTRY ID="SQL-SET"> +<REFMETA> +<REFENTRYTITLE> +SET +</REFENTRYTITLE> +<REFMISCINFO>SQL - Language Statements</REFMISCINFO> +</REFMETA> +<REFNAMEDIV> +<REFNAME> +SET +</REFNAME> +<REFPURPOSE> + Set run-time parameters for session +</REFPURPOSE> +<REFSYNOPSISDIV> +<REFSYNOPSISDIVINFO> +<DATE>1998-08-31</DATE> +</REFSYNOPSISDIVINFO> +<SYNOPSIS> +<REPLACEABLE CLASS="PARAMETER"> +</REPLACEABLE> +SET <REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> { TO | = } { '<REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>' | DEFAULT } + +SET TIME ZONE { '<REPLACEABLE CLASS="PARAMETER">timezone</REPLACEABLE>' | LOCAL }; +</SYNOPSIS> + +<REFSECT2 ID="R2-SQL-SET-1"> +<REFSECT2INFO> +<DATE>1998-08-31</DATE> +</REFSECT2INFO> +<TITLE> +Inputs +</TITLE> +<PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> +</TERM> +<LISTITEM> +<para> +Settable global parameter. + +<varlistentry> +<term> +<REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE> +</term> +<listitem> +<PARA> +New value of parameter. +</variablelist> + +<para> + The possible variables and allowed values are: + +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +DateStyle +</TERM> +<LISTITEM> +<PARA> + +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +ISO +</TERM> +<LISTITEM> +<PARA> +use ISO 8601-style dates and times +<VARLISTENTRY> +<TERM> +SQL +</TERM> +<LISTITEM> +<PARA> +use Oracle/Ingres-style dates and times +<VARLISTENTRY> +<TERM> +Postgres +</TERM> +<LISTITEM> +<PARA> +use traditional <productname>Postgres</productname> format +<VARLISTENTRY> +<TERM> +European +</TERM> +<LISTITEM> +<PARA> +use dd/mm/yyyy for numeric date representations. +<VARLISTENTRY> +<TERM> +NonEuropean +</TERM> +<LISTITEM> +<PARA> +use mm/dd/yyyy for numeric date representations. +<VARLISTENTRY> +<TERM> +German +</TERM> +<LISTITEM> +<PARA> +use dd.mm.yyyy for numeric date representations. +<VARLISTENTRY> +<TERM> +US +</TERM> +<LISTITEM> +<PARA> +same as 'NonEuropean' +<VARLISTENTRY> +<TERM> +default +</TERM> +<LISTITEM> +<PARA> +restores the default values ('US,Postgres') +</varlistentry> +</variablelist> + +<para> + Date format initialization my be done by: +<simplelist> +<member> +Setting PGDATESTYLE environment variable. + +<member> +Running postmaster using -oe parameter to set + dates to the 'European' convention. +Note that this affects only the some combinations of date styles; for example +the ISO style is not affected by this parameter. +<member> +Changing variables in +<filename>src/backend/utils/init/globals.c</filename>. +</simplelist> + +<para> +The variables in <filename>globals.c</filename> which can be changed are: +<programlisting> +bool EuroDates = false + true +int DateStyle = USE_ISO_DATES + USE_POSTGRES_DATES + USE_ISO_DATES + USE_SQL_DATES + USE_GERMAN_DATES +</programlisting> + +</varlistentry> + +<varlistentry> +<term> +TIMEZONE +</term> +<listitem> +<para> + The possible values for timezone depends on your operating + system. For example on Linux /usr/lib/zoneinfo contains the + database of timezones. +<para> + Here are some valid values for timezone: + +<variablelist> +<varlistentry> +<term> +'PST8PDT' +</term> +<listitem> +<para> +set the timezone for California +<varlistentry> +<term> +'Portugal' +</term> +<listitem> +<para> +set time zone for Portugal. +<varlistentry> +<term> +'Europe/Rome' +</term> +<listitem> +<para> +set time zone for Italy. +<varlistentry> +<term> +DEFAULT +</term> +<listitem> +<para> +set time zone to your local timezone +(value of the TZ environment variable). +</variablelist> + +<para> + If an invalid time zone is specified, the time zone + becomes GMT (on most systems anyway). +<para> + A frontend which uses libpq may be initialized by setting the PGTZ + environment variable. +<para> + The second syntax shown above, allows one to set the timezone + with a syntax similar to SQL92 <command>SET TIME ZONE</command>. + The LOCAL keyword is just an alternate form + of DEFAULT for SQL92 compatibility. + +</varlistentry> +</variablelist> + +There are also several internal or optimization parameters which can be specified +by the <command>SET</command> command: + +<variablelist> +<varlistentry> +<term> +COST_HEAP +</term> +<listitem> +<para> +Sets the default cost of a heap scan for use by the optimizer. + +<variablelist> +<varlistentry> +<term> +<replaceable class="parameter">float4</replaceable> +</term> +<listitem> +<para> +Set the cost of a heap scan to the specified floating point value. + +<varlistentry> +<term> +DEFAULT +</term> +<listitem> +<para> +Sets the cost of a heap scan to the default value. +</variablelist> + +<para> + The frontend may be initialized by setting the PGCOSTHEAP + environment variable. + +<varlistentry> +<term> +COST_INDEX +</term> +<listitem> +<para> +Sets the default cost of an index scan for use by the optimizer. + +<variablelist> +<varlistentry> +<term> +<replaceable class="parameter">float4</replaceable> +</term> +<listitem> +<para> +Set the cost of an index scan to the specified floating point value. + +<varlistentry> +<term> +DEFAULT +</term> +<listitem> +<para> +Sets the cost of an index scan to the default value. +</variablelist> + +<para> + The frontend may be initialized by setting the PGCOSTINDEX + environment variable. + +<varlistentry> +<term> +GEQO +</term> +<listitem> +<para> +Sets the threshold for using the genetic optimizer algorithm. + +<variablelist> +<varlistentry> +<term> +On +</term> +<listitem> +<para> +enables the genetic optimizer algorithm + for statements with 8 or more tables. +<varlistentry> +<term> +On=# +</term> +<listitem> +<para> +enables the genetic optimizer algorithm + for statements with # or more tables. +<varlistentry> +<term> +Off +</term> +<listitem> +<para> +disables the genetic optimizer algorithm. +<varlistentry> +<term> +DEFAULT +</term> +<listitem> +<para> +Equivalent to specifying <command>SET GEQO=On</command> +</varlistentry> +</variablelist> + +<para> + This algorithm is on by default, which used GEQO for + statements of eight or more tables. + (See the chapter on GEQO in the Programmer's Guide +for more information). + +<para> + The frontend may be initialized by setting PGGEQO + environment variable. +</varlistentry> + +<varlistentry> +<term> +R_PLANS +</term> +<listitem> +<para> +Determines whether right-hand plan evaluation is allowed: + +<variablelist> +<varlistentry> +<term> +On +</term> +<listitem> +<para> +enables right-hand evaluation of plans. + +<varlistentry> +<term> +Off +</term> +<listitem> +<para> +disables right-hand evaluation of plans. + +<varlistentry> +<term> +DEFAULT +</term> +<listitem> +<para> +Equivalent to specifying <command>SET R_PLANS=Off</command>. +</variablelist> + +<para> + It may be useful when joining big relations with + small ones. This algorithm is off by default. + It's not used by GEQO anyway. +<para> + The frontend may be initialized by setting the PGRPLANS + environment variable. +</varlistentry> + +<varlistentry> +<term> +R_PLANS +</term> +<listitem> +<para> +Determines whether right-hand plan evaluation is allowed: + +<variablelist> +<varlistentry> +<term> +On +</term> +<listitem> +<para> +enables right-hand evaluation of plans. + +<varlistentry> +<term> +Off +</term> +<listitem> +<para> +disables right-hand evaluation of plans. + +<varlistentry> +<term> +DEFAULT +</term> +<listitem> +<para> +Equivalent to specifying <command>SET R_PLANS=Off</command>. +</variablelist> + +<para> + It may be useful when joining big relations with + small ones. This algorithm is off by default. + It's not used by GEQO anyway. +<para> + The frontend may be initialized by setting the PGRPLANS + environment variable. + +</VARLISTENTRY> +</VARIABLELIST> +</REFSECT2> + +<REFSECT2 ID="R2-SQL-SET-2"> +<REFSECT2INFO> +<DATE>1998-08-31</DATE> +</REFSECT2INFO> +<TITLE> +Outputs +</TITLE> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM> + SET VARIABLE +</TERM> +<LISTITEM> +<PARA> + Message returned if successfully. + +<VARLISTENTRY> +<TERM> + WARN: Bad value for <replaceable class="parameter">variable</replaceable> (<replaceable class="parameter">value</replaceable>) +</TERM> +<LISTITEM> +<PARA> + If fails to set variable. +</VARLISTENTRY> +</VARIABLELIST> + +</REFSECT2> +</REFSYNOPSISDIV> + +<REFSECT1 ID="R1-SQL-SET-1"> +<REFSECT1INFO> +<DATE>1998-08-31</DATE> +</REFSECT1INFO> +<TITLE> +Description +</TITLE> +<PARA> +SET will modify configuration parameters for variable during + a session. + +<para> + Current values can be obtained using SHOW statement, and values + can be restored to the defaults using RESET statement. + Parameters and values are case-insensitive. Note that the value + field is always specified as a string, so is enclosed in + single-quotes. +<para> + SET TIME ZONE changes the session's default time zone offset. + A SQL-session always begins with an initial default time zone + offset. + The SET TIME ZONE statement is used to change the default + time zone offset for the current SQL session. + +<REFSECT2 ID="R2-SQL-SET-3"> +<REFSECT2INFO> +<DATE>1998-08-31</DATE> +</REFSECT2INFO> +<TITLE> +Notes +</TITLE> +<PARA> +The <command>SET <replaceable class="parameter">variable</replaceable></command> + statement is a <productname>Postgres</productname> language extension. + +<para> + Refer to SHOW/RESET statements to show/reset variable values. + +</REFSECT2> +</REFSECT1> + +<REFSECT1 ID="R1-SQL-SET-2"> +<TITLE> +Usage +</TITLE> +<PARA> +</PARA> +<ProgramListing> + --Set the style of date to ISO: + -- + SET DATESTYLE TO 'ISO'; +</programlisting> +<programlisting> + --Set GEQO to default: + -- + SET GEQO = DEFAULT; +</programlisting> +<programlisting> + --Turn on right-hand evaluation of plans: + -- + SET R_PLANS TO 'on'; +</programlisting> +<programlisting> + --set the timezone for Berkeley, California: + SET TIME ZONE 'PST8PDT'; + + SELECT CURRENT_TIMESTAMP AS today; + + today + ---------------------- + 1998-03-31 07:41:21-08 +</programlisting> +<programlisting> + --set the timezone for Italy: + SET TIME ZONE 'Europe/Rome'; + + SELECT CURRENT_TIMESTAMP AS today; + + today + ---------------------- + 1998-03-31 17:41:31+02 +</ProgramListing> + +</REFSECT1> + +<REFSECT1 ID="R1-SQL-SET-3"> +<TITLE> +Compatibility +</TITLE> +<PARA> +</PARA> + +<REFSECT2 ID="R2-SQL-SET-4"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +SQL92 +</TITLE> +<PARA> + There is no +<command>SET <replaceable class="parameter">variable</replaceable></command> + in SQL92. + + The SQL92 syntax for SET TIME ZONE is slightly different, + allowing only a single integer value for time zone specification: + +<programlisting> +SET TIME ZONE { interval_value_expression | LOCAL } +</programlisting> + +</REFENTRY> + +<!-- +<REPLACEABLE CLASS="PARAMETER"> +</REPLACEABLE> +<ReturnValue></ReturnValue> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM>• +</TERM> +<LISTITEM> +<PARA> +</PARA> +</LISTITEM> +</VARLISTENTRY> +</VARIABLELIST> +<PARA> +</PARA> +--> diff --git a/doc/src/sgml/ref/show.sgml b/doc/src/sgml/ref/show.sgml new file mode 100644 index 00000000000..73d60fc7c09 --- /dev/null +++ b/doc/src/sgml/ref/show.sgml @@ -0,0 +1,150 @@ +<REFENTRY ID="SQL-SHOW"> +<REFMETA> +<REFENTRYTITLE> +SHOW +</REFENTRYTITLE> +<REFMISCINFO>SQL - Language Statements</REFMISCINFO> +</REFMETA> +<REFNAMEDIV> +<REFNAME> +SHOW +</REFNAME> +<REFPURPOSE> +Shows run-time parameters for session +</REFPURPOSE> +<REFSYNOPSISDIV> +<REFSYNOPSISDIVINFO> +<DATE>1998-08-31</DATE> +</REFSYNOPSISDIVINFO> +<SYNOPSIS> +SHOW <REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> +</SYNOPSIS> + +<REFSECT2 ID="R2-SQL-SHOW-1"> +<REFSECT2INFO> +<DATE>1998-08-31</DATE> +</REFSECT2INFO> +<TITLE> +Inputs +</TITLE> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> +</TERM> +<LISTITEM> +<PARA> + Refer to the SET statement for more information on available + variables. +</VARLISTENTRY> +</VARIABLELIST> + +</REFSECT2> + +<REFSECT2 ID="R2-SQL-SHOW-2"> +<REFSECT2INFO> +<DATE>1998-08-31</DATE> +</REFSECT2INFO> +<TITLE> +Outputs +</TITLE> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +NOTICE: <REPLACEABLE CLASS="PARAMETER">variable</REPLACEABLE> is <ReturnValue>value</ReturnValue> +SHOW VARIABLE +</TERM> +<LISTITEM> +<PARA> + Message returned if successfully. +</listitem> +<VARLISTENTRY> +<TERM> +NOTICE: Unrecognized variable <ReturnValue>value</ReturnValue> +<LISTITEM> +<PARA> +Message returned if <ReturnValue>value</ReturnValue> does not exist. +</PARA> +</LISTITEM> +</VARLISTENTRY> +</VARIABLELIST> +</REFSECT2> +</REFSYNOPSISDIV> + +<REFSECT1 ID="R1-SQL-SHOW-1"> +<REFSECT1INFO> +<DATE>1998-08-31</DATE> +</REFSECT1INFO> +<TITLE> +Description +</TITLE> +<PARA> + SHOW will display the current configuration parameters for + variable during a session. + +<para> + The session can be configured using SET statement, and values + can be restored to the defaults using RESET statement. + Parameters and values are case-insensitive. + +<REFSECT2 ID="R2-SQL-SHOW-3"> +<REFSECT2INFO> +<DATE>1998-08-31</DATE> +</REFSECT2INFO> +<TITLE> +Notes +</TITLE> +<PARA> + The SHOW statement is a <productname>Postgres</productname> + language extension. + +<para> + Refer to the SET/RESET statements to set/reset variable values. + See also the SET TIME ZONE statement. + +<para> + If the TZ environment variable is not set the SHOW TIME ZONE statement + gives the message: Time zone is unknown +</REFSECT2> +</REFSECT1> + +<REFSECT1 ID="R1-SQL-SHOW-2"> +<TITLE> +Usage +</TITLE> +<PARA> +<ProgramListing> + -- show DateStyle; + SHOW DateStyle; + NOTICE:DateStyle is Postgres with US (NonEuropean) conventions + + -- show Geqo; + SHOW GEQO; + NOTICE:GEQO is ON +</ProgramListing> + +</REFSECT1> + +<REFSECT1 ID="R1-SQL-SHOW-3"> +<TITLE> +Compatibility +</TITLE> +<PARA> +</PARA> + +<REFSECT2 ID="R2-SQL-SHOW-4"> +<REFSECT2INFO> +<DATE>1998-08-31</DATE> +</REFSECT2INFO> +<TITLE> +SQL92 +</TITLE> +<PARA> + There is no SET statement defined in <acronym>SQL92</acronym>. +</refsect2> +</refsect1> +</REFENTRY> diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml new file mode 100644 index 00000000000..4fcb282ef07 --- /dev/null +++ b/doc/src/sgml/ref/update.sgml @@ -0,0 +1,190 @@ +<REFENTRY ID="SQL-UPDATE"> +<REFMETA> +<REFENTRYTITLE> +UPDATE +</REFENTRYTITLE> +<REFMISCINFO>SQL - Language Statements</REFMISCINFO> +</REFMETA> +<REFNAMEDIV> +<REFNAME> +UPDATE +</REFNAME> +<REFPURPOSE> +Replaces values of columns in a table +</REFPURPOSE> + +<REFSYNOPSISDIV> +<REFSYNOPSISDIVINFO> +<DATE>1998-04-15</DATE> +</REFSYNOPSISDIVINFO> +<SYNOPSIS> +UPDATE <REPLACEABLE CLASS="PARAMETER">table</replaceable> SET <REPLACEABLE CLASS="PARAMETER">column</replaceable> = <REPLACEABLE CLASS="PARAMETER">expression</replaceable> [, ...] + [FROM <REPLACEABLE CLASS="PARAMETER">fromlist</REPLACEABLE>] + [WHERE <REPLACEABLE CLASS="PARAMETER">condition</REPLACEABLE>] +</SYNOPSIS> + +<REFSECT2 ID="R2-SQL-UPDATE-1"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +Inputs +</TITLE> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">table</replaceable> +</TERM> +<LISTITEM> +<PARA> + The name of an existing table. +</LISTITEM> +</VARLISTENTRY> +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">column</replaceable> +</TERM> +<LISTITEM> +<PARA> +The name of a column in <REPLACEABLE CLASS="PARAMETER">table</replaceable>. +</LISTITEM> +</VARLISTENTRY> +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">expression</replaceable> +</TERM> +<LISTITEM> +<PARA> + A valid expression or value to assign to column. +</LISTITEM> +</VARLISTENTRY> +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">fromlist</REPLACEABLE> +</TERM> +<LISTITEM> +<PARA> + A <productname>Postgres</productname> + non-standard extension to allow columns + from other tables to appear in the WHERE condition. +</LISTITEM> +</VARLISTENTRY> +<VARLISTENTRY> +<TERM> +<REPLACEABLE CLASS="PARAMETER">condition</REPLACEABLE> +</TERM> +<LISTITEM> +<PARA> + Refer to the SELECT statement for a further description + of the WHERE clause. +</LISTITEM> +</VARLISTENTRY> +</VARIABLELIST> +</REFSECT2> + +<REFSECT2 ID="R2-SQL-UPDATE-2"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +Outputs +</TITLE> +<PARA> +</PARA> +<VARIABLELIST> +<VARLISTENTRY> +<TERM> +UPDATE <replaceable class="parameter">#</replaceable> +</TERM> +<LISTITEM> +<PARA> + Message returned if successful. +The <replaceable class="parameter">#</replaceable> + means the number of rows updated. +If <replaceable class="parameter">#</replaceable> + is equal 0 no rows are updated. +</LISTITEM> +</VARLISTENTRY> +</VARIABLELIST> + +</REFSECT2> +</REFSYNOPSISDIV> + +<REFSECT1 ID="R1-SQL-UPDATE-1"> +<REFSECT1INFO> +<DATE>1998-04-15</DATE> +</REFSECT1INFO> +<TITLE> +Description +</TITLE> +<PARA> + UPDATE changes the values of the columns specified for + all rows which satisfy condition. Only the columns + to be modified need appear as column. + +<PARA> + Array references use the same syntax found in SELECT. + That is, either single array elements, a range of array + elements or the entire array may be replaced with a single + query. + +<PARA> + You must have write access to the table in order to modify + it, as well as read access to any table whose values are + mentioned in the WHERE condition. + +</REFSECT1> + +<REFSECT1 ID="R1-SQL-UPDATE-2"> +<TITLE> +Usage +</TITLE> +<PARA> +</PARA> +<ProgramListing> + --Change word "Drama" with "Dramatic" on column kind: + -- + UPDATE films + SET kind = 'Dramatic' + WHERE kind = 'Drama'; + + SELECT * FROM films WHERE kind = 'Dramatic' OR kind = 'Drama'; + + code |title |did| date_prod|kind |len + -----+-------------+---+----------+----------+------ + BL101|The Third Man|101|1949-12-23|Dramatic | 01:44 + P_302|Becket |103|1964-02-03|Dramatic | 02:28 + M_401|War and Peace|104|1967-02-12|Dramatic | 05:57 + T_601|Yojimbo |106|1961-06-16|Dramatic | 01:50 + DA101|Das Boot |110|1981-11-11|Dramatic | 02:29 +</ProgramListing> + +</REFSECT1> + +<REFSECT1 ID="R1-SQL-UPDATE-3"> +<TITLE> +Compatibility +</TITLE> +<PARA> +</PARA> + +<REFSECT2 ID="R2-SQL-UPDATE-4"> +<REFSECT2INFO> +<DATE>1998-04-15</DATE> +</REFSECT2INFO> +<TITLE> +SQL92 +</TITLE> +<PARA> + SQL92 defines a different syntax for positioned UPDATE statement: + +<programlisting> + UPDATE table SET column = expression [, ...] + WHERE CURRENT OF <replaceable class="parameter">cursor</replaceable> +</programlisting> + + where <replaceable class="parameter">cursor</replaceable> + identifies an open cursor. +</REFENTRY> |