--
-- UPDATE syntax tests
--
CREATE EXTENSION sqlite_fdw;
CREATE SERVER sqlite_svr FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database '/tmp/sqlitefdw_test_core.db');
CREATE FOREIGN TABLE update_test (
    i   INT OPTIONS (key 'true'),
    a   INT DEFAULT 10,
    b   INT,
    c   TEXT
) SERVER sqlite_svr;
INSERT INTO update_test VALUES (1, 5, 10, 'foo');
INSERT INTO update_test(i, b, a) VALUES (2, 15, 10);
SELECT * FROM update_test;
 i | a  | b  |  c  
---+----+----+-----
 1 |  5 | 10 | foo
 2 | 10 | 15 | 
(2 rows)

UPDATE update_test SET a = DEFAULT, b = DEFAULT;
SELECT * FROM update_test;
 i | a  | b |  c  
---+----+---+-----
 1 | 10 |   | foo
 2 | 10 |   | 
(2 rows)

-- aliases for the UPDATE target table
UPDATE update_test AS t SET b = 10 WHERE t.a = 10;
SELECT * FROM update_test;
 i | a  | b  |  c  
---+----+----+-----
 1 | 10 | 10 | foo
 2 | 10 | 10 | 
(2 rows)

UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM update_test;
 i | a  | b  |  c  
---+----+----+-----
 1 | 10 | 20 | foo
 2 | 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;
 i |  a  | b  |  c  
---+-----+----+-----
 1 | 100 | 20 | foo
 2 | 100 | 20 | 
(2 rows)

-- fail, wrong data type:
UPDATE update_test SET a = v.* FROM (VALUES(100, 20)) AS v(i, j)
  WHERE update_test.b = v.j;
ERROR:  column "a" is of type integer but expression is of type record
LINE 1: UPDATE update_test SET a = v.* FROM (VALUES(100, 20)) AS v(i...
                                   ^
HINT:  You will need to rewrite or cast the expression.
--
-- Test multiple-set-clause syntax
--
INSERT INTO update_test SELECT i+2,a,b+1,c FROM update_test;
SELECT * FROM update_test;
 i |  a  | b  |  c  
---+-----+----+-----
 1 | 100 | 20 | foo
 2 | 100 | 20 | 
 3 | 100 | 21 | foo
 4 | 100 | 21 | 
(4 rows)

UPDATE update_test SET (c,b,a) = ('bugle', b+11, DEFAULT) WHERE c = 'foo';
SELECT * FROM update_test;
 i |  a  | b  |   c   
---+-----+----+-------
 1 |  10 | 31 | bugle
 2 | 100 | 20 | 
 3 |  10 | 32 | bugle
 4 | 100 | 21 | 
(4 rows)

UPDATE update_test SET (c,b) = ('car', a+b), a = a + 1 WHERE a = 10;
SELECT * FROM update_test;
 i |  a  | b  |  c  
---+-----+----+-----
 1 |  11 | 41 | car
 2 | 100 | 20 | 
 3 |  11 | 42 | car
 4 | 100 | 21 | 
(4 rows)

-- fail, multi assignment to same column:
UPDATE update_test SET (c,b) = ('car', a+b), b = a + 1 WHERE a = 10;
ERROR:  multiple assignments to same column "b"
-- uncorrelated sub-select:
UPDATE update_test
  SET (b,a) = (select a,b from update_test where b = 41 and c = 'car')
  WHERE a = 100 AND b = 20;
SELECT * FROM update_test;
 i |  a  | b  |  c  
---+-----+----+-----
 1 |  11 | 41 | car
 2 |  41 | 11 | 
 3 |  11 | 42 | car
 4 | 100 | 21 | 
(4 rows)

-- correlated sub-select:
UPDATE update_test o
  SET (b,a) = (select a+1,b from update_test i
               where i.a=o.a and i.b=o.b and i.c is not distinct from o.c);
SELECT * FROM update_test;
 i | a  |  b  |  c  
---+----+-----+-----
 1 | 41 |  12 | car
 2 | 11 |  42 | 
 3 | 42 |  12 | car
 4 | 21 | 101 | 
(4 rows)

-- fail, multiple rows supplied:
UPDATE update_test SET (b,a) = (select a+1,b from update_test);
ERROR:  more than one row returned by a subquery used as an expression
-- set to null if no rows supplied:
UPDATE update_test SET (b,a) = (select a+1,b from update_test where a = 1000)
  WHERE a = 11;
SELECT * FROM update_test;
 i | a  |  b  |  c  
---+----+-----+-----
 1 | 41 |  12 | car
 2 |    |     | 
 3 | 42 |  12 | car
 4 | 21 | 101 | 
(4 rows)

-- *-expansion should work in this context:
UPDATE update_test SET (a,b) = ROW(v.*) FROM (VALUES(21, 100)) AS v(i, j)
  WHERE update_test.a = v.i;
-- you might expect this to work, but syntactically it's not a RowExpr:
UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 101)) AS v(i, j)
  WHERE update_test.a = v.i;
ERROR:  source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression
LINE 1: UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 101)) ...
                                        ^
-- if an alias for the target table is specified, don't allow references
-- to the original table name
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
ERROR:  invalid reference to FROM-clause entry for table "update_test"
LINE 1: UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a...
                                        ^
HINT:  Perhaps you meant to reference the table alias "t".
-- Make sure that we can update to a TOASTed value.
UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
SELECT a, b, char_length(c) FROM update_test;
 a  |  b  | char_length 
----+-----+-------------
 41 |  12 |       10000
    |     |            
 42 |  12 |       10000
 21 | 100 |            
(4 rows)

--drop all foreign tables
DO $d$
declare
  l_rec record;
begin
  for l_rec in (select foreign_table_schema, foreign_table_name 
                from information_schema.foreign_tables) loop
     execute format('drop foreign table %I.%I cascade;', l_rec.foreign_table_schema, l_rec.foreign_table_name);
  end loop;
end;
$d$;
DROP SERVER sqlite_svr;
DROP EXTENSION sqlite_fdw CASCADE;