\documentstyle[art10]{article}
%\documentstyle[11pt,fullpage]{article}
\setlength{\oddsidemargin}{0 in}
\setlength{\evensidemargin}{0 in}
\setlength{\topmargin}{-0.4 in}
\setlength{\textwidth}{6.5 in}
\setlength{\textheight}{9 in}

\setlength{\parskip}{10pt}
\newcommand{\tab}{\hspace*{.25in}}

\begin{document}
\begin{center}
{\Large {\bf {\sc From Postquel To Sql}}}

\begin{small}
\begin{sc}
Andrew Yu and Jolly Chen\\
Computer Science Division\\
University of California, Berkeley\\
\end{sc}
{\em May 15, 1995}
\end{small}
\end{center}

\parbox[t]{3.25in} {
\tab \underline{\sc POSTQUEL}
}
\hspace{.25in}
\parbox[t]{3.25in} {
\tab \underline{\sc SQL}
}

\vspace{.25in}
{\bf {\Large 1. Data Definition}}
\vspace{.15in}

\indent
\parbox[t]{3.25in} {
{\tt createdb} {\em dbname}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CREATE DATABASE} {\em dbname}
}

\parbox[t]{3.25in} {
{\tt destroydb} {\em dbname}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DROP DATABASE} {\em dbname}
}

\parbox[t]{3.25in} {
{\tt create} {\em relname} {\tt (}{\em attr1}{\tt =}{\em type1} \{{\tt ,} {\em attri}{\tt =}{\em
typei}\}{\tt )}\\
\tab {\tt inherits(}{\em relname1} \{{\tt ,} {\em relnamei} ...\}{\tt )}\\
\tab {\tt archive {\tt =}} {\em archive\_mode}\\
\tab {\tt store = "}{\em smgr\_name}{\tt "}\\
\tab {\tt arch\_store = "}{\em smgr\_name}{\tt "}\\
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CREATE TABLE} {\em relname} {\tt (}\\
\tab {\em attr1} {\em type1} \{{\tt ,} {\em attri} {\em typei} ...\}\\
\tab {\tt )}\\
\tab {\tt INHERITS(}{\em relname1} \{{\tt ,} {\em relnamei} ...\}{\tt )}\\
\tab {\tt ARCHIVE =} {\em archive\_mode}\\
\tab {\tt STORE = "}{\em smgr\_name}{\tt "}\\
\tab {\tt ARCH\_STORE = "}{\em smgr\_name}{\tt "}{\tt ;}\\
}

\parbox[t]{3.25in} {
{\tt destroy} {\em relname1} \{{\tt ,} {\em relnamei} ...\}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DROP TABLE} {\em relname1} \{{\tt ,} {\em relnamei} ...\}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt addattr (}{\em attr1}{\tt =}{\em type1} \{{\tt ,} {\em attri} {\tt =} {\em typei} ...\}
{\tt )}\\
\tab {\tt to} {\em relname} [*]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt ALTER TABLE} {\em relname} [*]\\
\tab {\tt ADD COLUMN} {\em attr1} {\em type1}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt rename} {\em attr1} {\tt in} {\em relname} [{\tt *}] {\tt to} {\em attr2}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt ALTER TABLE} {\em relname} [{\tt *}]\\
\tab {\tt RENAME} [{\tt COLUMN}] {\em attr1} {\tt TO} {\em attr2}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt rename} {\em relname1} {\tt to} {\em relname2}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt ALTER TABLE} {\em relname1}\\
\tab {\tt RENAME TO} {\em relname2}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt change acl} [{\tt group}$|${\tt user}] {\em name}[{\tt +-=}][
{\tt arwR}]\\
\tab {\em {\em relname1}} \{{\tt ,} {\em relnamei} ...\}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\em same}
}

\parbox[t]{3.25in} {
{\tt define view} {\em view\_name}\\
\tab {\tt (} [ {\em dom\_name1} {\tt =}] {\em expr1}\\
\tab  \{{\tt ,} [{\em dom\_namei} {\tt =}] {\em expri} ... \} {\tt )}\\
\tab [{\tt from} {\em from\_list}]\\
\tab [{\tt where} {\em qual}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CREATE VIEW} {\em view\_name} {\tt AS} \\
\tab {\tt SELECT} {\em expr1} [{\tt AS} {\em attr1}] \{{\tt ,} {\em expri} [{\tt AS}
{\em attri}] ...\} \\
\tab [{\tt FROM} {\em from\_list}]\\
\tab [{\tt WHERE} {\em qual}]{\tt ;}
}

\parbox[t]{3.25in} {
{\tt remove view} {\em viewname}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DROP VIEW} {\em viewname}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt define} [{\tt archive}] {\tt index} {\em indexname}\\ 
\tab {\tt on} {\em relname} {\tt using} {\em access\_method}\\
\tab {\tt (}\{{\em attr1} $|$ {\em funcname}{\tt (}{\em attr1} \{{\tt ,} {\em
attri} ...\})\} \\
\tab {\em type\_class1}{\tt )}\\
\tab [{\tt where} {\em qual}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CREATE INDEX} {\em indexname}\\ 
\tab {\tt ON} {\em relname} {\tt USING} {\em access\_method}\\
\tab {\tt (}\{{\em attr1} $|$ {\em funcname}{\tt (}{\em attr1} \{{\tt ,} {\em
attri} ...\})\} \\
\tab {\em type\_class1}{\tt )}\\
\tab [{\tt WHERE} {\em qual}]{\tt ;}
}

\parbox[t]{3.25in} {
{\tt extend index} {\em indexname} [{\tt where} {\em qual}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\em same}
}

\parbox[t]{3.25in} {
{\tt remove index} {\em indexname}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DROP INDEX} {\em indexname}{\tt ;}
}


\vspace{.25in}
{\bf {\Large 2. User Defined Types, Functions, etc.}}
\vspace{.15in}

\parbox[t]{3.25in} {
{\tt define type} {\em typename} {\tt (}\\
\tab {\tt internallength =} ({\em number} $|$ {\tt variable}){\tt ,}\\
\tab [ {\tt externallength =} ({\em number} $|$ {\tt variable}){\tt ,} ]\\
\tab {\tt input =} {\em input\_function}{\tt ,}\\
\tab {\tt output =} {\em output\_function}\\
\tab [{\tt , element =} {\em typename}]\\
\tab [{\tt , delimiter =} {\em character}]\\
\tab [{\tt , default =} {\tt "}{\em string}{\tt "} ]\\
\tab [{\tt , send =} {\em send\_function} ]\\
\tab [{\tt , receive =} {\em receive\_function} ]\\
\tab [{\tt , passedbyvalue}]\\
\tab {\tt )}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CREATE TYPE} {\em typename} {\tt (}\\
\tab {\tt internallength =} ({\em number} $|$ {\tt variable}){\tt ,}\\
\tab [ {\tt externallength =} ({\em number} $|$ {\tt variable}){\tt ,} ]\\
\tab {\tt input =} {\em input\_function}{\tt ,}\\
\tab {\tt output =} {\em output\_function}\\
\tab [{\tt , alignment =} \{ {\tt double} $|$ {\tt int} \} ]\\
\tab [{\tt , element =} {\em typename}]\\
\tab [{\tt , delimiter =} {\em character}]\\
\tab [{\tt , default =} {\tt "}{\em string}{\tt "} ]\\
\tab [{\tt , send =} {\em send\_function} ]\\
\tab [{\tt , receive =} {\em receive\_function} ]\\
\tab [{\tt , passedbyvalue}]\\
\tab {\tt )}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt remove type} {\em typename}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DROP TYPE} {\em typename}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt define function} {\em function\_name} {\tt (}\\
\tab  {\tt language =} \{{\tt "c"} $|$ {\tt "postquel"}\}{\tt ,}\\
\tab  {\tt returntype =} {\em type-r}\\
\tab  [ {\tt , iscachable} ]\\
\tab  [ {\tt , trusted =} \{{\tt "t"} $|$ {\tt "f"}\} ]\\
\tab  [ {\tt , percall\_cpu =} {\tt costly}\{{\tt !}*\} ]\\
\tab  [ {\tt , perbyte\_cpu =} {\tt costly}\{{\tt !}*\} ]\\
\tab  [ {\tt , outin\_ratio =}  {\em percentage} ]\\
\tab  [ {\tt , byte\_pct =}  {\em percentage} ]\\
\tab  {\tt )}\\
\tab  {\tt arg is} {\tt (} [ {\em type1}  \{ {\tt ,} {\em typei} ... \} ] {\tt )}\\
\tab  {\tt as} \{{\tt "}{\em objectfilename}{\tt "} $|$ 
	{\tt "}{\em postquel-queries}{\tt "}\}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CREATE FUNCTION} {\em function\_name} \\
\tab {\tt (} [ {\em type1}  \{ {\tt ,} {\em typei} ... \} ] {\tt )}\\
\tab {\tt RETURNS} {\em type-r}\\
\tab  {\tt AS} \{{\tt "}{\em objectfilename}{\tt "} $|$ 
	  {\tt "}{\em sql-queries}{\tt "}\}\\
\tab  {\tt LANGUAGE} \{{\tt "c"} $|$ {\tt "sql"}\}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt remove function} {\em funcname} {\tt (}[{\em type1} \{{\tt ,} {\em typei} ...\}]{\tt )}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DROP FUNCTION} {\em funcname} {\tt (}[{\em type1} \{{\tt ,} {\em typei} ...\}]{\tt )}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt define operator} {\em operator\_name} {\tt (}\\
\tab [ {\tt arg1 =} {\em type-1} ]\\
\tab [ {\tt , arg2 =} {\em type-2} ]\\
\tab {\tt , procedure =} {\em func\_name}\\
\tab [{\tt , precedence =} {\em number} ]\\
\tab [{\tt , associativity =} \{{\tt left} $|$ 
{\tt right} $|$ {\tt none} $|$ {\tt any}\} ]\\
\tab [{\tt , commutator =} {\em com\_op} ]\\
\tab [{\tt , negator =} {\em neg\_op} ]\\
\tab [{\tt , restrict =} {\em res\_proc} ]\\
\tab [{\tt , hashes}]\\
\tab [{\tt , join =} {\em join\_proc} ]\\
\tab [{\tt , sort =} {\em sor\_op1} \{{\tt ,} {\em sor\_opi} ... \} ]\\
\tab {\tt )}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CREATE OPERATOR} {\em operator\_name} {\tt (}\\
\tab [ {\tt leftarg =} {\em type-1} ]\\
\tab [ {\tt , rightarg =} {\em type-2} ]\\
\tab {\tt , procedure =} {\em func\_name}\\
\tab [{\tt , commutator =} {\em com\_op} ]\\
\tab [{\tt , negator =} {\em neg\_op} ]\\
\tab [{\tt , restrict =} {\em res\_proc} ]\\
\tab [{\tt , hashes}]\\
\tab [{\tt , join =} {\em join\_proc} ]\\
\tab [{\tt , sort =} {\em sor\_op1} \{{\tt ,} {\em sor\_opi} ... \} ]\\
\tab {\tt )}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt remove operator} {\em opname}{\tt (}\{{\em ltype}{\tt ,} 
{\em rtype} $|$ {\em type}\}{\tt )}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DROP OPERATOR} {\em opname}{\tt (}\{{\em ltype} $|$ {\tt none}\}{\tt ,} \\
\tab \{{\em rtype} $|$ {\tt none}\}{\tt )}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt define aggregate} {\em agg-name} [{\tt as}] {\tt (}\\
\tab [{\tt sfunc1 =} {\em state-transition-function-1}{\tt ,}\\
\tab {\tt basetype =} {\em data-type}{\tt ,}\\
\tab {\tt stype1 =} {\em sfunc1-return-type}]\\
\tab [{\tt , sfunc2 =} {\em state-transition-function-2}{\tt ,}\\
\tab  {\tt stype2 =} {\em sfunc2-return-type}]\\
\tab [{\tt , finalfunc =} {\em final-function}]\\
\tab [{\tt , initcond1 =} {\em initial-condition-1}]\\
\tab [{\tt , initcond2 =} {\em initial-condition-2}]\\
\tab {\tt )}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CREATE AGGREGATE} {\em agg-name} [{\tt AS}] {\tt (}\\
\tab [{\tt sfunc1 =} {\em state-transition-function-1}{\tt ,}\\
\tab {\tt basetype =} {\em data-type}{\tt ,}\\
\tab {\tt stype1 =} {\em sfunc1-return-type}]\\
\tab [{\tt , sfunc2 =} {\em state-transition-function-2}{\tt ,}\\
\tab  {\tt stype2 =} {\em sfunc2-return-type}]\\
\tab [{\tt , finalfunc =} {\em final-function}]\\
\tab [{\tt , initcond1 =} {\em initial-condition-1}]\\
\tab [{\tt , initcond2 =} {\em initial-condition-2}]\\
\tab {\tt )}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt remove aggregate} {\em aggname}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DROP AGGREGATE} {\em aggname}{\tt ;}
}

\parbox[t]{3.25in} {
{\tt define} [{\tt instance} $|$ {\tt rewrite}] {\tt rule} {\em rule\_name}\\
\tab {\tt is on} \{ {\tt retrieve} $|$ {\tt replace} $|$ {\tt delete} $|$
	{\tt append} \}\\
\tab {\tt to} {\em object} [{\tt where} {\em qual}]\\
\tab {\tt do} [{\tt instead}]\\
\tab [{\em action} $|$ {\tt nothing} $|$ [{\em actions} ...]]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CREATE RULE} {\em rule\_name} {\tt AS}\\
\tab {\tt on} \{ {\tt select} $|$ {\tt update} $|$ {\tt delete} $|$
	{\tt insert} \}\\
\tab {\tt TO} {\em object} [{\tt WHERE} {\em qual}]\\
\tab {\tt DO} [{\tt instead}] [{\em action} $|$ {\tt nothing} $|$ [{\em actions} ...]]{\tt ;}
}

\parbox[t]{3.25in} {
{\tt remove} [{\tt rewrite}] {\tt rule} {\em rulename}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DROP RULE} {\em rulename}{\tt ;}
}

\vspace{.25in}
{\bf {\Large 3. Data Manipulation}}
\vspace{.15in}


\parbox[t]{3.25in} {
{\tt append} {\em relname} \\
\tab {\tt (}{\em attr1}{\tt =}{\em expr1} \{{\tt ,} {\em attri}{\tt =}{\em expri} ...\}{\tt )}\\
\tab [{\tt from} {\em from\_list}]\\
\tab [{\tt where} {\em qual}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt INSERT} {\tt INTO} {\em relname} [ {\tt (}{\em attr1} \{{\tt ,}
{\em attri} ...\}{\tt )} ] \\
\tab \{ {\tt VALUES} {\tt (}{\em expr1} \{{\tt ,} {\em expri} ...\}{\tt )}{\tt
;} $|$\\
\tab {\tt SELECT} {\em expr1} \{, {\em expri} ...\}\\
\tab \tab [{\tt FROM} {\em from\_clause}]\\
\tab \tab [{\tt WHERE} {\em qual}] {\tt ;} \}
}

\parbox[t]{3.25in} {
{\tt delete} {\em instance\_var}\\ 
\tab [{\tt from} {\em from\_list}]\\
\tab [{\tt where} {\em qual}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DELETE} {\tt FROM} {\em relname}\\ 
\tab [{\tt WHERE} {\em qual}]{\tt ;}
}

\parbox[t]{3.25in} {
{\tt replace} {\em instance\_var}\\
\tab {\tt (}{\em attr1}{\tt =}{\em expr1} \{{\tt ,} {\em attri}{\tt =}{\em expri} ...\}{\tt )}\\
\tab [{\tt from} {\em from\_list}]\\
\tab [{\tt where} {\em qual}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt UPDATE} {\em relname}\\ 
\tab  {\tt SET} {\em attr1}{\tt =}{\em expr1} \{{\tt ,} {\em attri}{\tt =}{\em expri} ...\}\\
\tab  [{\tt WHERE} {\em qual}]{\tt ;}
}

\parbox[t]{3.25in} {
{\tt retrieve} [{\tt into} {\em relname}] [{\tt unique}]\\
\tab {\tt (}{\em attr1}{\tt =}{\em expr1} \{{\tt ,} {\em attri}{\tt =}{\em expri} ...\}{\tt )}\\
\tab [{\tt from} {\em from\_list}]\\
\tab [{\tt where} {\em qual}]\\
\tab [{\tt sort by} {\em attr1} [{\tt using} {\em op1}] \{{\tt ,} {\em attri}
[{\tt using} {\em opi}] ... \}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt SELECT} [{\tt distinct}] \\
\tab {\em expr1} [{\tt AS} {\em attr1}] \{{\tt ,} {\em expri} [{\tt AS}
{\em attri}] ...\} \\
\tab [{\tt INTO TABLE} {\em relname}]\\
\tab [{\tt FROM} {\em from\_list}]\\
\tab [{\tt WHERE} {\em qual}]\\
\tab [{\tt GROUP BY} {\em attr1} \{{\tt,} {\em attri ...}\}]\\
\tab [{\tt ORDER BY} {\em attr1} [{\tt USING} {\em op1}] \{{\tt ,} {\em attri}
[{\tt USING} {\em opi}] ... \}]{\tt ;}
}

\parbox[t]{3.25in} {
{\tt retrieve portal} {\em portalname} [{\tt unique}]\\
\tab {\tt (}{\em attr1}{\tt =}{\em expr1} \{{\tt ,} {\em attri}{\tt =}{\em expri} ...\}{\tt )}\\
\tab [{\tt from} {\em from\_list}]\\
\tab [{\tt where} {\em qual}]\\
\tab [{\tt sort by} {\em attr1} [{\tt using} {\em op1}] \{{\tt ,} {\em attri}
[{\tt using} {\em opi}]\}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt DECLARE} {\em portalname} [{\tt BINARY}] {\tt CURSOR FOR}\\
\tab {\tt SELECT} [{\tt distinct}] \\
\tab {\em expr1} [{\tt AS} {\em attr1}] \{{\tt ,} {\em expri} [{\tt AS}
{\em attri}] ...\} \\
\tab [{\tt FROM} {\em from\_list}]\\
\tab [{\tt WHERE} {\em qual}]\\
\tab [{\tt ORDER BY} {\em attr1} [{\tt using} {\em op1}] {{\tt ,} {\em attri}
[{\tt using} {\em opi}]}]{\tt ;}
}

\parbox[t]{3.25in} {
{\tt fetch} [{\tt forward} $|$ {\tt backward}]\\
\tab [{\em number} $|$ {\tt all}] [{\tt in} {\em portalname}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\em same}
}

\parbox[t]{3.25in} {
{\tt close} {\em portalname}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CLOSE} {\em cursorname}{\tt ;}
}

\vspace{.25in}
{\bf {\Large 4. Transactions}}
\vspace{.15in}

\parbox[t]{3.25in} {
{\tt begin} [{\tt transaction}]\\
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\em same}
}

\parbox[t]{3.25in} {
{\tt abort} [{\tt transaction}]\\
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\em same}
}

\parbox[t]{3.25in} {
{\tt end} [{\tt transaction}]\\
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\em same}
}

\vspace{.25in}
{\bf {\Large 5. Miscellaneous}}
\vspace{.15in}

\parbox[t]{3.25in} {
{\tt copy} [{\tt binary}] [{\tt nonulls}] {\em relname}\\
\tab \{{\tt to}$|${\tt from}\} \{{\em filename}$|${\tt stdin}$|${\tt stdout}\}\\
\tab [{\tt using} {\em maprelname}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt COPY} [{\tt BINARY}] [{\tt NONULLS}] {\em relname}\\
\tab \{{\tt TO}$|${\tt FROM}\} \{{\em filename}$|${\tt stdin}$|${\tt stdout}\}\\
}

\parbox[t]{3.25in} {
{\tt load} {\em filename}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\em same}
}

\parbox[t]{3.25in} {
{\tt purge} {\em relname} [{\tt before} {\em abstime}] [{\tt after} {\em reltime}]
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\em same}
}

\parbox[t]{3.25in} {
{\tt vacuum}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt VACUUM} [ {\em relname} ]
}

\parbox[t]{3.25in} {
{\em none}
}
\hspace{.25in}
\parbox[t]{3.25in} {
{\tt CLUSTER} {\em indexname} {\tt ON} {\em relname}
}


\vspace{1in}

\noindent
notes: 
\begin{enumerate}
\item
keywords are case-insensitive. WHERE is the same as wHeRe
\item
comments starts with {\tt --}
\item
some postquel queries are inexpressible in SQL (eg. inheritence queries in
updates and deletes)
\end{enumerate}
\end{document}