Add tests for COPY in PL/pgSQL

This stresses the error handling of COPY inside SPI which does not
support the operation using stdin or stdout, and these scenarios were
not tested up to now.

Author: Mark Dilger
Discussion: https://postgr.es/m/a6e9b130-7fd5-387b-4ec5-89bda24373ab@gmail.com

6 files changed, 158 insertions, 2 deletions

diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile
index 4d57bf5f1e5..e0bcccf39e2 100644
--- a/src/pl/plpgsql/src/Makefile
+++ b/src/pl/plpgsql/src/Makefile
@@ -32,8 +32,8 @@ DATA = plpgsql.control plpgsql--1.0.sql plpgsql--unpackaged--1.0.sql
 
 REGRESS_OPTS = --dbname=$(PL_TESTDB)
 
-REGRESS = plpgsql_call plpgsql_control plpgsql_domain plpgsql_record \
-	plpgsql_cache plpgsql_transaction plpgsql_trap \
+REGRESS = plpgsql_call plpgsql_control plpgsql_copy plpgsql_domain \
+	plpgsql_record plpgsql_cache plpgsql_transaction plpgsql_trap \
 	plpgsql_trigger plpgsql_varprops
 
 # where to find gen_keywordlist.pl and subsidiary files
diff --git a/src/pl/plpgsql/src/data/copy1.data b/src/pl/plpgsql/src/data/copy1.data
new file mode 100644
index 00000000000..5d8478facb1
--- /dev/null
+++ b/src/pl/plpgsql/src/data/copy1.data
@@ -0,0 +1,3 @@
+1	1.1
+2	2.2
+3	3.3
diff --git a/src/pl/plpgsql/src/expected/.gitignore b/src/pl/plpgsql/src/expected/.gitignore
new file mode 100644
index 00000000000..13e59187210
--- /dev/null
+++ b/src/pl/plpgsql/src/expected/.gitignore
@@ -0,0 +1 @@
+/plpgsql_copy.out
diff --git a/src/pl/plpgsql/src/input/plpgsql_copy.source b/src/pl/plpgsql/src/input/plpgsql_copy.source
new file mode 100644
index 00000000000..b7bcbb7d17f
--- /dev/null
+++ b/src/pl/plpgsql/src/input/plpgsql_copy.source
@@ -0,0 +1,62 @@
+CREATE TABLE copy1 (a int, b float);
+
+-- COPY TO/FROM not authorized from client.
+DO LANGUAGE plpgsql $$
+BEGIN
+  COPY copy1 TO stdout;
+END;
+$$;
+DO LANGUAGE plpgsql $$
+BEGIN
+  COPY copy1 FROM stdin;
+END;
+$$;
+DO LANGUAGE plpgsql $$
+BEGIN
+  EXECUTE 'COPY copy1 TO stdout';
+END;
+$$;
+DO LANGUAGE plpgsql $$
+BEGIN
+  EXECUTE 'COPY copy1 FROM stdin';
+END;
+$$;
+
+-- Valid cases
+-- COPY FROM
+DO LANGUAGE plpgsql $$
+BEGIN
+  COPY copy1 FROM '@abs_srcdir@/data/copy1.data';
+END;
+$$;
+SELECT * FROM copy1 ORDER BY 1;
+TRUNCATE copy1;
+DO LANGUAGE plpgsql $$
+BEGIN
+  EXECUTE 'COPY copy1 FROM ''@abs_srcdir@/data/copy1.data''';
+END;
+$$;
+SELECT * FROM copy1 ORDER BY 1;
+
+-- COPY TO
+-- Copy the data externally once, then process it back to the table.
+DO LANGUAGE plpgsql $$
+BEGIN
+  COPY copy1 TO '@abs_builddir@/results/copy1.data';
+END;
+$$;
+TRUNCATE copy1;
+DO LANGUAGE plpgsql $$
+BEGIN
+  COPY copy1 FROM '@abs_builddir@/results/copy1.data';
+END;
+$$;
+DO LANGUAGE plpgsql $$
+BEGIN
+  EXECUTE 'COPY copy1 FROM ''@abs_builddir@/results/copy1.data''';
+END;
+$$;
+
+SELECT * FROM copy1 ORDER BY 1;
+
+DROP TABLE copy1;
diff --git a/src/pl/plpgsql/src/output/plpgsql_copy.source b/src/pl/plpgsql/src/output/plpgsql_copy.source
new file mode 100644
index 00000000000..78c9a751800
--- /dev/null
+++ b/src/pl/plpgsql/src/output/plpgsql_copy.source
@@ -0,0 +1,89 @@
+CREATE TABLE copy1 (a int, b float);
+-- COPY TO/FROM not authorized from client.
+DO LANGUAGE plpgsql $$
+BEGIN
+  COPY copy1 TO stdout;
+END;
+$$;
+ERROR:  cannot COPY to/from client in PL/pgSQL
+CONTEXT:  PL/pgSQL function inline_code_block line 3 at SQL statement
+DO LANGUAGE plpgsql $$
+BEGIN
+  COPY copy1 FROM stdin;
+END;
+$$;
+ERROR:  cannot COPY to/from client in PL/pgSQL
+CONTEXT:  PL/pgSQL function inline_code_block line 3 at SQL statement
+DO LANGUAGE plpgsql $$
+BEGIN
+  EXECUTE 'COPY copy1 TO stdout';
+END;
+$$;
+ERROR:  cannot COPY to/from client in PL/pgSQL
+CONTEXT:  PL/pgSQL function inline_code_block line 3 at EXECUTE
+DO LANGUAGE plpgsql $$
+BEGIN
+  EXECUTE 'COPY copy1 FROM stdin';
+END;
+$$;
+ERROR:  cannot COPY to/from client in PL/pgSQL
+CONTEXT:  PL/pgSQL function inline_code_block line 3 at EXECUTE
+-- Valid cases
+-- COPY FROM
+DO LANGUAGE plpgsql $$
+BEGIN
+  COPY copy1 FROM '@abs_builddir@/data/copy1.data';
+END;
+$$;
+SELECT * FROM copy1 ORDER BY 1;
+ a |  b  
+---+-----
+ 1 | 1.1
+ 2 | 2.2
+ 3 | 3.3
+(3 rows)
+
+TRUNCATE copy1;
+DO LANGUAGE plpgsql $$
+BEGIN
+  EXECUTE 'COPY copy1 FROM ''@abs_builddir@/data/copy1.data''';
+END;
+$$;
+SELECT * FROM copy1 ORDER BY 1;
+ a |  b  
+---+-----
+ 1 | 1.1
+ 2 | 2.2
+ 3 | 3.3
+(3 rows)
+
+-- COPY TO
+-- Copy the data externally once, then process it back to the table.
+DO LANGUAGE plpgsql $$
+BEGIN
+  COPY copy1 TO '@abs_builddir@/results/copy1.data';
+END;
+$$;
+TRUNCATE copy1;
+DO LANGUAGE plpgsql $$
+BEGIN
+  COPY copy1 FROM '@abs_builddir@/results/copy1.data';
+END;
+$$;
+DO LANGUAGE plpgsql $$
+BEGIN
+  EXECUTE 'COPY copy1 FROM ''@abs_builddir@/results/copy1.data''';
+END;
+$$;
+SELECT * FROM copy1 ORDER BY 1;
+ a |  b  
+---+-----
+ 1 | 1.1
+ 1 | 1.1
+ 2 | 2.2
+ 2 | 2.2
+ 3 | 3.3
+ 3 | 3.3
+(6 rows)
+
+DROP TABLE copy1;
diff --git a/src/pl/plpgsql/src/sql/.gitignore b/src/pl/plpgsql/src/sql/.gitignore
new file mode 100644
index 00000000000..210bee188ef
--- /dev/null
+++ b/src/pl/plpgsql/src/sql/.gitignore
@@ -0,0 +1 @@
+/plpgsql_copy.sql