aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/test/regress/expected/insert.out17
-rw-r--r--src/test/regress/expected/select.out61
-rw-r--r--src/test/regress/expected/update.out12
-rw-r--r--src/test/regress/sql/insert.sql9
-rw-r--r--src/test/regress/sql/select.sql28
-rw-r--r--src/test/regress/sql/update.sql9
6 files changed, 136 insertions, 0 deletions
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 10d5ce49d58..b4e6c306b4c 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -37,4 +37,21 @@ select * from inserttest;
| 7 | testing
(4 rows)
+--
+-- 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;
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index f1facdc8e9d..6fcc88860d6 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -452,3 +452,64 @@ select foo from (select 'xyzzy',1,null) as foo;
(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)
+
+-- try VALUES in a subquery
+select * from onek
+ where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))
+ order by unique1;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
+ 20 | 306 | 0 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 20 | 0 | 1 | UAAAAA | ULAAAA | OOOOxx
+ 99 | 101 | 1 | 3 | 9 | 19 | 9 | 99 | 99 | 99 | 99 | 18 | 19 | VDAAAA | XDAAAA | HHHHxx
+(3 rows)
+
+-- VALUES is also legal as a standalone query or a set-operation member
+VALUES (1,2), (3,4+4), (7,77.7);
+ column1 | column2
+---------+---------
+ 1 | 2
+ 3 | 8
+ 7 | 77.7
+(3 rows)
+
+VALUES (1,2), (3,4+4), (7,77.7)
+UNION ALL
+SELECT 2+2, 57
+UNION ALL
+SELECT * FROM int8_tbl;
+ column1 | column2
+------------------+-------------------
+ 1 | 2
+ 3 | 8
+ 7 | 77.7
+ 4 | 57
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(9 rows)
+
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index 9849e905392..55d82628e28 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -39,6 +39,18 @@ SELECT * FROM update_test;
10 | 20
(2 rows)
+--
+-- 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
-- to the original table name
BEGIN;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 90badf37b78..8a9ccce2ea2 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -19,4 +19,13 @@ insert into inserttest (col1) values (1, 2);
insert into inserttest (col1) values (DEFAULT, DEFAULT);
select * from inserttest;
+
+--
+-- 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;
diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql
index 223ccb58271..2c813a9d8e5 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -110,3 +110,31 @@ SELECT p.name, p.age FROM person* p ORDER BY age using >, name;
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;
+
+-- try VALUES in a subquery
+select * from onek
+ where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))
+ order by unique1;
+
+-- VALUES is also legal as a standalone query or a set-operation member
+VALUES (1,2), (3,4+4), (7,77.7);
+
+VALUES (1,2), (3,4+4), (7,77.7)
+UNION ALL
+SELECT 2+2, 57
+UNION ALL
+SELECT * FROM int8_tbl;
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
index 99fd74bca79..51007b2ff18 100644
--- a/src/test/regress/sql/update.sql
+++ b/src/test/regress/sql/update.sql
@@ -25,6 +25,15 @@ UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM update_test;
+--
+-- 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
-- to the original table name
BEGIN;
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-04 21:27:54 +0000