-- ===================================================================
-- test INSERT proxy creation functionality
-- ===================================================================
-- use transaction to permit multiple calls to proxy function in one session
BEGIN;
-- use "unorthodox" object names to test quoting
CREATE SCHEMA "A$AP Mob"
	CREATE TABLE "Dr. Bronner's ""Magic"" Soaps" (
		id bigint PRIMARY KEY,
		data text NOT NULL DEFAULT 'lorem ipsum'
	);
\set insert_target '"A$AP Mob"."Dr. Bronner''s ""Magic"" Soaps"'
-- create proxy and save proxy table name
SELECT create_insert_proxy_for_table(:'insert_target') AS proxy_tablename
\gset
-- insert to proxy, relying on default value
INSERT INTO pg_temp.:"proxy_tablename" (id) VALUES (1);
-- copy some rows into the proxy
COPY pg_temp.:"proxy_tablename" FROM stdin;
-- verify rows were copied to target
SELECT * FROM :insert_target ORDER BY id ASC;
 id |            data             
----+-----------------------------
  1 | lorem ipsum
  2 | dolor sit amet
  3 | consectetur adipiscing elit
  4 | sed do eiusmod
  5 | tempor incididunt ut
  6 | labore et dolore
(6 rows)

-- and not to proxy
SELECT count(*) FROM pg_temp.:"proxy_tablename";
 count 
-------
     0
(1 row)

ROLLBACK;
-- test behavior with distributed table, (so no transaction)
CREATE TABLE insert_target (
	id bigint PRIMARY KEY,
	data text NOT NULL DEFAULT 'lorem ipsum'
);
-- squelch WARNINGs that contain worker_port
SET client_min_messages TO ERROR;
SELECT master_create_distributed_table('insert_target', 'id');
 master_create_distributed_table 
---------------------------------
 
(1 row)

SELECT master_create_worker_shards('insert_target', 2, 1);
 master_create_worker_shards 
-----------------------------
 
(1 row)

CREATE TEMPORARY SEQUENCE rows_inserted;
SELECT create_insert_proxy_for_table('insert_target', 'rows_inserted') AS proxy_tablename
\gset
-- insert to proxy, again relying on default value
INSERT INTO pg_temp.:"proxy_tablename" (id) VALUES (1);
-- test copy with bad row in middle
COPY pg_temp.:"proxy_tablename" FROM stdin;
ERROR:  could not modify any active placements
CONTEXT:  SQL statement "INSERT INTO public.insert_target (id,data) VALUES ($1,$2)"
PL/pgSQL function pg_temp_2.copy_to_insert() line 3 at EXECUTE statement
COPY insert_target_insert_proxy, line 6: "7	\N"
-- verify rows were copied to distributed table
SELECT * FROM insert_target ORDER BY id ASC;
 id |            data             
----+-----------------------------
  1 | lorem ipsum
  2 | dolor sit amet
  3 | consectetur adipiscing elit
  4 | sed do eiusmod
  5 | tempor incididunt ut
  6 | labore et dolore
(6 rows)

-- the counter should match the number of rows stored
SELECT currval('rows_inserted');
 currval 
---------
       6
(1 row)

SET client_min_messages TO DEFAULT;