postgres.git / summary / log / commit / refs
commit 1858b105b05fcded43e9f2b767dec7268431043b
Author: Michael Paquier <michael@paquier.xyz>
Date: Sat Nov 09 05:50:20 2019 +0000
First released in: 13.0
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
src/pl/plpgsql/src/Makefile | 4 +-
src/pl/plpgsql/src/data/copy1.data | 3 +
src/pl/plpgsql/src/expected/.gitignore | 1 +
src/pl/plpgsql/src/input/plpgsql_copy.source | 62 +++++++++++++++++++
src/pl/plpgsql/src/output/plpgsql_copy.source | 89 +++++++++++++++++++++++++++
src/pl/plpgsql/src/sql/.gitignore | 1 +
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
[parent: aae50236e4ce]