pg.ddx.io pgsql-docs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Gavin Sherry <swm@linuxworld.com.au>
To: Joe Conway <mail@joeconway.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-patches@postgresql.org
Subject: Re: Values list-of-targetlists patch for comments (was Re:
Date: Thu, 3 Aug 2006 15:31:06 +1000 (EST)
Message-ID: <Pine.LNX.4.58.0608031523090.4590@linuxworld.com.au> (raw )
In-Reply-To: <44CFFD2D.8040906@joeconway.com >
References: <44B69778.5050104@dunslane.net >
<44B7ADFC.90205@dunslane.net >
<44BC0432.3040509@joeconway.com >
<44BC661F.80609@joeconway.com >
<44BCD06C.3060409@calorieking.com >
<44BCF1D4.3010807@dunslane.net >
<5842.1153246741@sss.pgh.pa.us >
<44BD8854.9060506@calorieking.com >
<15212.1153279954@sss.pgh.pa.us >
<44BDB2F5.7080003@joeconway.com >
<44BEA710.6060700@joeconway.com >
<23198.1153367887@sss.pgh.pa.us >
<44BF0382.2010501@joeconway.com >
<23454.1153370214@sss.pgh.pa.us >
<44BF122F.1080200@joeconway.com >
<23927.1153373971@sss.pgh.pa.us >
<44C00E1E.1000405@joeconway.com >
<14047.1153442773@sss.pgh.pa.us >
<44C4451A.4010906@joeconway.com >
<11495.1153757531@sss.pgh.pa.us >
<44C4F625.2050304@joeconway.com >
<12029.1153760969@sss.pgh.pa.us >
<44CC0A66.6070903@joeconway.com >
<16429.1154463351@sss.pgh.pa.us >
<44CFBC78.2020508@joeconway.com >
<27359.1154474195@sss.pgh.pa.us >
<Pine.LNX.4.58.0608021005220.14057@linuxworld.com.au >
<44CFFD2D.8040906@joeconway.com >
Docs and regression tests attached.
One slightly annoying thing is this:
---
regression=# declare foo cursor with hold for VALUES(1,2), (3, 4);
DECLARE CURSOR
regression=# declare foo2 cursor with hold for (VALUES(1,2), (3, 4)) as
foo(i, j);
ERROR: syntax error at or near "as"
LINE 1: ...e foo2 cursor with hold for (VALUES(1,2), (3, 4)) as foo(i, ...
---
Now, we can just rewrite the second query as:
---
declare foo2 cursor with hold for select * from (VALUES(1,2), (3, 4)) as
foo(i, j);
---
but it's not immediately obvious. Not worth busting up the grammar for it,
though. And, it's not spec.
Gavin
Attachments:
values_doc_test.diff (text/plain, 16.6K) download
Index: doc/src/sgml/ref/declare.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/declare.sgml,v
retrieving revision 1.37
diff -c -p -r1.37 declare.sgml
*** doc/src/sgml/ref/declare.sgml 26 Feb 2006 03:20:46 -0000 1.37
--- doc/src/sgml/ref/declare.sgml 3 Aug 2006 04:18:28 -0000
*************** DECLARE liahona CURSOR FOR SELECT * FROM
*** 275,280 ****
--- 275,288 ----
See <xref linkend="sql-fetch" endterm="sql-fetch-title"> for more
examples of cursor usage.
</para>
+
+ <para>
+ The cursor <replaceable class="parameter">query</> clause can also
+ be a <literal>VALUES</> list:
+ <programlisting>
+ DECLARE cols CURSOR FOR VALUES(1,2), (3,4);
+ </programlisting>
+ </para>
</refsect1>
<refsect1>
Index: doc/src/sgml/ref/delete.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/delete.sgml,v
retrieving revision 1.26
diff -c -p -r1.26 delete.sgml
*** doc/src/sgml/ref/delete.sgml 22 Jan 2006 05:20:33 -0000 1.26
--- doc/src/sgml/ref/delete.sgml 3 Aug 2006 03:26:58 -0000
*************** DELETE FROM [ ONLY ] <replaceable class=
*** 117,122 ****
--- 117,128 ----
in the <replaceable class="PARAMETER">usinglist</replaceable>,
unless you wish to set up a self-join.
</para>
+
+ <para>
+ The <replaceable class="PARAMETER">usinglist</> may also contain a
+ <literal>VALUES</> list, evaluating to one or more rows. These
+ rows may also be referenced in the <literal>WHERE</> clause.
+ </para>
</listitem>
</varlistentry>
*************** DELETE FROM films WHERE kind <> 'M
*** 191,196 ****
--- 197,213 ----
DELETE FROM films;
</programlisting>
</para>
+
+ <para>
+ Delete films made after 1990 which are 'Horror' and films made
+ after 2000 which are 'Crime'. To do this, we use a <literal>VALUES</>
+ list in the <literal>USING</> clause.
+ <programlisting>
+ DELETE FROM films USING (VALUES('1990-01-01, 'Horror'), ('2000-01-01', 'Crime))
+ AS det (year, kind) WHERE films.date_prod >= det.year AND
+ films.kind = det.kind;
+ </programlisting>
+ </para>
</refsect1>
<refsect1>
Index: doc/src/sgml/ref/insert.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/insert.sgml,v
retrieving revision 1.30
diff -c -p -r1.30 insert.sgml
*** doc/src/sgml/ref/insert.sgml 17 Nov 2005 22:14:51 -0000 1.30
--- doc/src/sgml/ref/insert.sgml 2 Aug 2006 22:40:14 -0000
*************** PostgreSQL documentation
*** 21,27 ****
<refsynopsisdiv>
<synopsis>
INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
! { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | <replaceable class="PARAMETER">query</replaceable> }
</synopsis>
</refsynopsisdiv>
--- 21,27 ----
<refsynopsisdiv>
<synopsis>
INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
! { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ( ... ) ] | <replaceable class="PARAMETER">query</replaceable> }
</synopsis>
</refsynopsisdiv>
*************** INSERT INTO <replaceable class="PARAMETE
*** 30,37 ****
<para>
<command>INSERT</command> inserts new rows into a table.
! One can insert a single row specified by value expressions,
! or several rows as a result of a query.
</para>
<para>
--- 30,37 ----
<para>
<command>INSERT</command> inserts new rows into a table.
! One can insert one or more rows specified by value expressions,
! or zero or more rows resulting from a query.
</para>
<para>
*************** INSERT INTO films VALUES
*** 162,167 ****
--- 162,177 ----
</para>
<para>
+ Insert multiple rows into a table <literal>films</>:
+
+ <programlisting>
+ INSERT INTO films VALUES
+ ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
+ ('HG120', 'The Dinner Game', 140, '1998-10-12', 'Comedy');
+ </programlisting>
+ </para>
+
+ <para>
In this example, the <literal>len</literal> column is
omitted and therefore it will have the default value:
Index: doc/src/sgml/ref/select.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v
retrieving revision 1.91
diff -c -p -r1.91 select.sgml
*** doc/src/sgml/ref/select.sgml 30 Apr 2006 18:30:38 -0000 1.91
--- doc/src/sgml/ref/select.sgml 2 Aug 2006 22:40:12 -0000
*************** SELECT [ ALL | DISTINCT [ ON ( <replacea
*** 35,40 ****
--- 35,41 ----
where <replaceable class="parameter">from_item</replaceable> can be one of:
[ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
+ ( VALUES ( <replaceable class="parameter">expression</replaceable> [, ... ] ) [, ( ... ) ] [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
<replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] | <replaceable class="parameter">column_definition</replaceable> [, ...] ) ]
<replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
*************** where <replaceable class="parameter">fro
*** 197,202 ****
--- 198,214 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>VALUES(<replaceable class="parameter">expression</replaceable> [, ...]) [, ...]</literal></term>
+ <listitem>
+ <para>
+ One or more rows may be constructed in the from clause. Each
+ <replaceable class="parameter">expression</> will be evaluated and
+ assigned to the corresponding column.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry>
<term><replaceable class="parameter">alias</replaceable></term>
*************** SELECT f.title, f.did, d.name, f.date_pr
*** 916,921 ****
--- 928,947 ----
</para>
<para>
+ To join the table <literal>films</> with a <literal>VALUES</> list:
+ <programlisting>
+ SELECT f.title, f.did, f.date_prod, f.kind
+ FROM films f, (VALUES('Horror'), ('Sci-Fi')) as k (kind)
+ WHERE f.kind = k.kind;
+ title | did | date_prod | kind
+ ------------------------------+-----+------------+--------
+ The Texas Chain Saw Massacre | 190 | 1974-06-11 | Horror
+ 2001: A Space Odyssey | 210 | 1968-08-24 | Sci-Fi
+ ...
+ </programlisting>
+ </para>
+
+ <para>
To sum the column <literal>len</literal> of all films and group
the results by <literal>kind</literal>:
Index: doc/src/sgml/ref/update.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/update.sgml,v
retrieving revision 1.37
diff -c -p -r1.37 update.sgml
*** doc/src/sgml/ref/update.sgml 8 Mar 2006 22:59:09 -0000 1.37
--- doc/src/sgml/ref/update.sgml 3 Aug 2006 03:27:07 -0000
*************** UPDATE [ ONLY ] <replaceable class="PARA
*** 134,139 ****
--- 134,145 ----
<replaceable>fromlist</>, unless you intend a self-join (in which
case it must appear with an alias in the <replaceable>fromlist</>).
</para>
+
+ <para>
+ The <replaceable class="PARAMETER">from_list</> may also contain a
+ <literal>VALUES</> list, evaluating to one or more rows. These
+ rows may also be referenced in the <literal>WHERE</> clause.
+ </para>
</listitem>
</varlistentry>
*************** UPDATE employees SET sales_count = sales
*** 233,238 ****
--- 239,255 ----
</para>
<para>
+ Perform an update on <literal>employees</> with a join against a
+ <literal>VALUES</> list:
+ <programlisting>
+ UPDATE employees SET salary = salary * increase
+ FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4))
+ as prof (depno, target, increase)
+ WHERE employees.sales >= prof.target and employees.depno = prof.depno;
+ </programlisting>
+ </para>
+
+ <para>
Attempt to insert a new stock item along with the quantity of stock. If
the item already exists, instead update the stock count of the existing
item. To do this without failing the entire transaction, use savepoints.
Index: src/test/regress/sql/insert.sql
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/test/regress/sql/insert.sql,v
retrieving revision 1.2
diff -c -p -r1.2 insert.sql
*** src/test/regress/sql/insert.sql 24 Apr 2002 02:22:54 -0000 1.2
--- src/test/regress/sql/insert.sql 3 Aug 2006 05:13:57 -0000
*************** insert into inserttest (col1, col2, col3
*** 18,22 ****
--- 18,29 ----
insert into inserttest (col1) values (1, 2);
insert into inserttest (col1) values (DEFAULT, DEFAULT);
+ --
+ -- VALUES test
+ --
+
+ insert into inserttest values(10, 20, '40'), (-1, 2, DEFAULT),
+ ((select 2), (select i from (values(3)) as foo (i)), 'values are fun!');
+
select * from inserttest;
drop table inserttest;
Index: src/test/regress/sql/select.sql
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/test/regress/sql/select.sql,v
retrieving revision 1.10
diff -c -p -r1.10 select.sql
*** src/test/regress/sql/select.sql 14 Dec 2005 16:28:32 -0000 1.10
--- src/test/regress/sql/select.sql 3 Aug 2006 05:04:15 -0000
*************** SELECT p.name, p.age FROM person* p ORDE
*** 110,112 ****
--- 110,125 ----
select foo from (select 1) as foo;
select foo from (select null) as foo;
select foo from (select 'xyzzy',1,null) as foo;
+
+ --
+ -- Test VALUES lists
+ --
+ select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j) WHERE
+ onek.unique1 = v.i and onek.stringu1 = v.j;
+
+ -- a more complex case
+ -- looks like we're coding lisp :-)
+ select * from onek, (values((select i from
+ (values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i)
+ order by i asc limit 1))) bar (i)
+ where onek.unique1 = bar.i;
Index: src/test/regress/sql/update.sql
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/test/regress/sql/update.sql,v
retrieving revision 1.3
diff -c -p -r1.3 update.sql
*** src/test/regress/sql/update.sql 22 Jan 2006 05:20:35 -0000 1.3
--- src/test/regress/sql/update.sql 3 Aug 2006 04:32:03 -0000
*************** SELECT * FROM update_test;
*** 23,28 ****
--- 23,35 ----
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
+ --
+ -- Test VALUES in FROM
+ --
+
+ UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
+ WHERE update_test.b = v.j;
+
SELECT * FROM update_test;
-- if an alias for the target table is specified, don't allow references
*************** SET LOCAL add_missing_from = false;
*** 32,35 ****
--- 39,43 ----
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
ROLLBACK;
+
DROP TABLE update_test;
Index: src/test/regress/expected/insert.out
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/test/regress/expected/insert.out,v
retrieving revision 1.7
diff -c -p -r1.7 insert.out
*** src/test/regress/expected/insert.out 25 Sep 2003 06:58:06 -0000 1.7
--- src/test/regress/expected/insert.out 3 Aug 2006 05:19:01 -0000
*************** insert into inserttest (col1) values (1,
*** 28,40 ****
ERROR: INSERT has more expressions than target columns
insert into inserttest (col1) values (DEFAULT, DEFAULT);
ERROR: INSERT has more expressions than target columns
select * from inserttest;
col1 | col2 | col3
! ------+------+---------
| 3 | testing
| 5 | testing
| 5 | test
| 7 | testing
! (4 rows)
drop table inserttest;
--- 28,48 ----
ERROR: INSERT has more expressions than target columns
insert into inserttest (col1) values (DEFAULT, DEFAULT);
ERROR: INSERT has more expressions than target columns
+ --
+ -- VALUES test
+ --
+ insert into inserttest values(10, 20, '40'), (-1, 2, DEFAULT),
+ ((select 2), (select i from (values(3)) as foo (i)), 'values are fun!');
select * from inserttest;
col1 | col2 | col3
! ------+------+-----------------
| 3 | testing
| 5 | testing
| 5 | test
| 7 | testing
! 10 | 20 | 40
! -1 | 2 | testing
! 2 | 3 | values are fun!
! (7 rows)
drop table inserttest;
Index: src/test/regress/expected/select.out
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/test/regress/expected/select.out,v
retrieving revision 1.14
diff -c -p -r1.14 select.out
*** src/test/regress/expected/select.out 14 Dec 2005 16:28:32 -0000 1.14
--- src/test/regress/expected/select.out 3 Aug 2006 04:50:36 -0000
*************** select foo from (select 'xyzzy',1,null)
*** 452,454 ****
--- 452,476 ----
(xyzzy,1,)
(1 row)
+ --
+ -- Test VALUES lists
+ --
+ select * from onek, (values(147, 'RFAAAA'), (931, 'VJAAAA')) as v (i, j) WHERE
+ onek.unique1 = v.i and onek.stringu1 = v.j;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i | j
+ ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+-----+--------
+ 147 | 0 | 1 | 3 | 7 | 7 | 7 | 47 | 147 | 147 | 147 | 14 | 15 | RFAAAA | AAAAAA | AAAAxx | 147 | RFAAAA
+ 931 | 1 | 1 | 3 | 1 | 11 | 1 | 31 | 131 | 431 | 931 | 2 | 3 | VJAAAA | BAAAAA | HHHHxx | 931 | VJAAAA
+ (2 rows)
+
+ -- a more complex case
+ -- looks like we're coding lisp :-)
+ select * from onek, (values((select i from
+ (values(10000), (2), (389), (1000), (2000), ((select 10029))) as foo(i)
+ order by i asc limit 1))) bar (i)
+ where onek.unique1 = bar.i;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i
+ ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---
+ 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx | 2
+ (1 row)
+
Index: src/test/regress/expected/update.out
===================================================================
RCS file: /usr/local/cvsroot/pgsql/src/test/regress/expected/update.out,v
retrieving revision 1.3
diff -c -p -r1.3 update.out
*** src/test/regress/expected/update.out 14 Mar 2006 22:48:25 -0000 1.3
--- src/test/regress/expected/update.out 3 Aug 2006 04:50:48 -0000
*************** SELECT * FROM update_test;
*** 32,42 ****
(2 rows)
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM update_test;
a | b
! ----+----
! 10 | 20
! 10 | 20
(2 rows)
-- if an alias for the target table is specified, don't allow references
--- 32,47 ----
(2 rows)
UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
+ --
+ -- Test VALUES in FROM
+ --
+ UPDATE update_test SET a=v.i FROM (VALUES(100, 20)) AS v(i, j)
+ WHERE update_test.b = v.j;
SELECT * FROM update_test;
a | b
! -----+----
! 100 | 20
! 100 | 20
(2 rows)
-- if an alias for the target table is specified, don't allow references
view thread (61+ messages) latest in thread
Message-ID: <Pine.LNX.4.58.0608031523090.4590@linuxworld.com.au>
Permalink: ../Pine.LNX.4.58.0608031523090.4590@linuxworld.com.au/
Also on: postgresql.org/message-id/Pine.LNX.4.58.0608031523090.4590@linuxworld.com.au
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: swm@linuxworld.com.au, mail@joeconway.com, tgl@sss.pgh.pa.us, pgsql-patches@postgresql.org
Subject: Re: Values list-of-targetlists patch for comments (was Re:
In-Reply-To: <Pine.LNX.4.58.0608031523090.4590@linuxworld.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox