<!-- $PostgreSQL: pgsql/doc/src/sgml/keywords.sgml,v 2.26 2009/09/22 23:43:37 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/keywords.sgml,v 2.27 2009/11/05 23:24:22 tgl Exp $ -->

<appendix id="sql-keywords-appendix">

<title><acronym>SQL</acronym> Key Words</title>

</row>

<row>

<entry><token>NEW</token></entry>

<entry>reserved</entry>

<entry></entry>

<entry>reserved</entry>

<entry>reserved</entry>

<entry>reserved</entry>

</row>

<row>

<entry><token>OLD</token></entry>

<entry>reserved</entry>

<entry></entry>

<entry>reserved</entry>

<entry>reserved</entry>

<entry>reserved</entry>

<!-- $PostgreSQL: pgsql/doc/src/sgml/rules.sgml,v 1.52 2008/12/16 03:12:08 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/rules.sgml,v 1.53 2009/11/05 23:24:22 tgl Exp $ -->

<chapter id="rules">

<title>The Rule System</title>

<note>

<para>

The two extra range

table entries for <literal>NEW</> and <literal>OLD</> (named <literal>*NEW*</> and <literal>*OLD*</> for

historical reasons in the printed query tree) you can see in

table entries for <literal>NEW</> and <literal>OLD</> that you can see in

the <structname>pg_rewrite</structname> entry aren't of interest

for <command>SELECT</command> rules.

</para>

SELECT s.sl_name, s.sl_avail,

s.sl_color, s.sl_len, s.sl_unit,

s.sl_len * u.un_fact AS sl_len_cm

FROM shoelace*OLD*, shoelace*NEW*,

FROM shoelaceold, shoelacenew,

shoelace_data s, unit u

WHERE s.sl_unit = u.un_name;

</programlisting>

</programlisting>

There is one difference however: the subquery's range table has two

extra entries <literal>shoelace*OLD*</> and <literal>shoelace*NEW*</>. These entries don't

extra entries <literal>shoelaceold</> and <literal>shoelacenew</>. These entries don't

participate directly in the query, since they aren't referenced by

the subquery's join tree or target list. The rewriter uses them

to store the access privilege check information that was originally present

the remaining range-table entries in the top query (in this example there

are no more), and it will recursively check the range-table entries in

the added subquery to see if any of them reference views. (But it

won't expand <literal>*OLD*</> or <literal>*NEW*</> &mdash; otherwise we'd have infinite recursion!)

won't expand <literal>old</> or <literal>new</> &mdash; otherwise we'd have infinite recursion!)

In this example, there are no rewrite rules for <literal>shoelace_data</> or <literal>unit</>,

so rewriting is complete and the above is the final result given to

the planner.

<programlisting>

INSERT INTO shoelace_log VALUES (

*NEW*.sl_name,*NEW*.sl_avail,

new.sl_name,new.sl_avail,

current_user, current_timestamp )

FROM shoelace_data*NEW*, shoelace_data*OLD*;

FROM shoelace_datanew, shoelace_dataold;

</programlisting>

(This looks a little strange since you cannot normally write

<literal>INSERT ... VALUES ... FROM</>. The <literal>FROM</>

clause here is just to indicate that there are range-table entries

in the query tree for <literal>*NEW*</> and <literal>*OLD*</>.

in the query tree for <literal>new</> and <literal>old</>.

These are needed so that they can be referenced by variables in

the <command>INSERT</command> command's query tree.)

</para>

<programlisting>

INSERT INTO shoelace_log VALUES (

*NEW*.sl_name,*NEW*.sl_avail,

new.sl_name,new.sl_avail,

current_user, current_timestamp )

FROM shoelace_data*NEW*, shoelace_data*OLD*,

FROM shoelace_datanew, shoelace_dataold,

<emphasis>shoelace_data shoelace_data</emphasis>;

</programlisting>

<programlisting>

INSERT INTO shoelace_log VALUES (

*NEW*.sl_name,*NEW*.sl_avail,

new.sl_name,new.sl_avail,

current_user, current_timestamp )

FROM shoelace_data*NEW*, shoelace_data*OLD*,

FROM shoelace_datanew, shoelace_dataold,

shoelace_data shoelace_data

<emphasis>WHERE*NEW*.sl_avail &lt;&gt;*OLD*.sl_avail</emphasis>;

<emphasis>WHEREnew.sl_avail &lt;&gt;old.sl_avail</emphasis>;

</programlisting>

(This looks even stranger, since <literal>INSERT ... VALUES</> doesn't have

<programlisting>

INSERT INTO shoelace_log VALUES (

*NEW*.sl_name,*NEW*.sl_avail,

new.sl_name,new.sl_avail,

current_user, current_timestamp )

FROM shoelace_data*NEW*, shoelace_data*OLD*,

FROM shoelace_datanew, shoelace_dataold,

shoelace_data shoelace_data

WHERE*NEW*.sl_avail &lt;&gt;*OLD*.sl_avail

WHEREnew.sl_avail &lt;&gt;old.sl_avail

<emphasis>AND shoelace_data.sl_name = 'sl7'</emphasis>;

</programlisting>

</para>

INSERT INTO shoelace_log VALUES (

<emphasis>shoelace_data.sl_name</emphasis>, <emphasis>6</emphasis>,

current_user, current_timestamp )

FROM shoelace_data*NEW*, shoelace_data*OLD*,

FROM shoelace_datanew, shoelace_dataold,

shoelace_data shoelace_data

WHERE <emphasis>6</emphasis> &lt;&gt;*OLD*.sl_avail

WHERE <emphasis>6</emphasis> &lt;&gt;old.sl_avail

AND shoelace_data.sl_name = 'sl7';

</programlisting>

INSERT INTO shoelace_log VALUES (

shoelace_data.sl_name, 6,

current_user, current_timestamp )

FROM shoelace_data*NEW*, shoelace_data*OLD*,

FROM shoelace_datanew, shoelace_dataold,

shoelace_data shoelace_data

WHERE 6 &lt;&gt; <emphasis>shoelace_data.sl_avail</emphasis>

AND shoelace_data.sl_name = 'sl7';

UPDATE shoelace

SET sl_avail = shoelace.sl_avail + shoelace_arrive.arr_quant

FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,

shoelace_ok*OLD*, shoelace_ok*NEW*,

shoelace_okold, shoelace_oknew,

shoelace shoelace

WHERE shoelace.sl_name = shoelace_arrive.arr_name;

</programlisting>

sl_len = shoelace.sl_len,

sl_unit = shoelace.sl_unit

FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,

shoelace_ok*OLD*, shoelace_ok*NEW*,

shoelace shoelace, shoelace*OLD*,

shoelace*NEW*, shoelace_data shoelace_data

shoelace_okold, shoelace_oknew,

shoelace shoelace, shoelaceold,

shoelacenew, shoelace_data shoelace_data

WHERE shoelace.sl_name = shoelace_arrive.arr_name

AND shoelace_data.sl_name = shoelace.sl_name;

</programlisting>

sl_len = s.sl_len,

sl_unit = s.sl_unit

FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,

shoelace_ok*OLD*, shoelace_ok*NEW*,

shoelace shoelace, shoelace*OLD*,

shoelace*NEW*, shoelace_data shoelace_data,

shoelace*OLD*, shoelace*NEW*,

shoelace_okold, shoelace_oknew,

shoelace shoelace, shoelaceold,

shoelacenew, shoelace_data shoelace_data,

shoelaceold, shoelacenew,

shoelace_data s, unit u

WHERE s.sl_name = shoelace_arrive.arr_name

AND shoelace_data.sl_name = s.sl_name;

current_user,

current_timestamp

FROM shoelace_arrive shoelace_arrive, shoelace_ok shoelace_ok,

shoelace_ok*OLD*, shoelace_ok*NEW*,

shoelace shoelace, shoelace*OLD*,

shoelace*NEW*, shoelace_data shoelace_data,

shoelace*OLD*, shoelace*NEW*,

shoelace_okold, shoelace_oknew,

shoelace shoelace, shoelaceold,

shoelacenew, shoelace_data shoelace_data,

shoelaceold, shoelacenew,

shoelace_data s, unit u,

shoelace_data*OLD*, shoelace_data*NEW*

shoelace_dataold, shoelace_datanew

shoelace_log shoelace_log

WHERE s.sl_name = shoelace_arrive.arr_name

AND shoelace_data.sl_name = s.sl_name

<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.137 2009/10/08 02:39:16 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.138 2009/11/05 23:24:22 tgl Exp $ -->

<chapter id="sql-syntax">

<title>SQL Syntax</title>

<para>

<replaceable>correlation</replaceable> is the name of a

table (possibly qualified with a schema name), or an alias for a table

defined by means of a <literal>FROM</literal> clause, or one of

the key words <literal>NEW</literal> or <literal>OLD</literal>.

(<literal>NEW</literal> and <literal>OLD</literal> can only appear in rewrite rules,

while other correlation names can be used in any SQL statement.)

defined by means of a <literal>FROM</literal> clause.

The correlation name and separating dot can be omitted if the column name

is unique across all the tables being used in the current query. (See also <xref linkend="queries">.)

</para>

rt_entry1=addRangeTableEntryForRelation(NULL,viewRel,

makeAlias("*OLD*",NIL),

makeAlias("old",NIL),

false, false);

rt_entry2=addRangeTableEntryForRelation(NULL,viewRel,

makeAlias("*NEW*",NIL),

makeAlias("new",NIL),

false, false);

rt_entry1->requiredPerms=0;