Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1858b10

Browse files
committed
Add tests for COPY in PL/pgSQL
This stresses the error handling of COPY inside SPI which does notsupport the operation using stdin or stdout, and these scenarios werenot tested up to now.Author: Mark DilgerDiscussion:https://postgr.es/m/a6e9b130-7fd5-387b-4ec5-89bda24373ab@gmail.com
1 parentaae5023 commit1858b10

File tree

6 files changed

+158
-2
lines changed

6 files changed

+158
-2
lines changed

‎src/pl/plpgsql/src/Makefile

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -32,8 +32,8 @@ DATA = plpgsql.control plpgsql--1.0.sql plpgsql--unpackaged--1.0.sql
3232

3333
REGRESS_OPTS = --dbname=$(PL_TESTDB)
3434

35-
REGRESS = plpgsql_call plpgsql_controlplpgsql_domain plpgsql_record\
36-
plpgsql_cache plpgsql_transaction plpgsql_trap\
35+
REGRESS = plpgsql_call plpgsql_controlplpgsql_copy plpgsql_domain\
36+
plpgsql_recordplpgsql_cache plpgsql_transaction plpgsql_trap\
3737
plpgsql_trigger plpgsql_varprops
3838

3939
# where to find gen_keywordlist.pl and subsidiary files

‎src/pl/plpgsql/src/data/copy1.data

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
11.1
2+
22.2
3+
33.3
Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
/plpgsql_copy.out
Lines changed: 62 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,62 @@
1+
CREATE TABLE copy1 (a int, b float);
2+
3+
-- COPY TO/FROM not authorized from client.
4+
DO LANGUAGE plpgsql $$
5+
BEGIN
6+
COPY copy1 TO stdout;
7+
END;
8+
$$;
9+
DO LANGUAGE plpgsql $$
10+
BEGIN
11+
COPY copy1 FROM stdin;
12+
END;
13+
$$;
14+
DO LANGUAGE plpgsql $$
15+
BEGIN
16+
EXECUTE 'COPY copy1 TO stdout';
17+
END;
18+
$$;
19+
DO LANGUAGE plpgsql $$
20+
BEGIN
21+
EXECUTE 'COPY copy1 FROM stdin';
22+
END;
23+
$$;
24+
25+
-- Valid cases
26+
-- COPY FROM
27+
DO LANGUAGE plpgsql $$
28+
BEGIN
29+
COPY copy1 FROM '@abs_srcdir@/data/copy1.data';
30+
END;
31+
$$;
32+
SELECT * FROM copy1 ORDER BY 1;
33+
TRUNCATE copy1;
34+
DO LANGUAGE plpgsql $$
35+
BEGIN
36+
EXECUTE 'COPY copy1 FROM ''@abs_srcdir@/data/copy1.data''';
37+
END;
38+
$$;
39+
SELECT * FROM copy1 ORDER BY 1;
40+
41+
-- COPY TO
42+
-- Copy the data externally once, then process it back to the table.
43+
DO LANGUAGE plpgsql $$
44+
BEGIN
45+
COPY copy1 TO '@abs_builddir@/results/copy1.data';
46+
END;
47+
$$;
48+
TRUNCATE copy1;
49+
DO LANGUAGE plpgsql $$
50+
BEGIN
51+
COPY copy1 FROM '@abs_builddir@/results/copy1.data';
52+
END;
53+
$$;
54+
DO LANGUAGE plpgsql $$
55+
BEGIN
56+
EXECUTE 'COPY copy1 FROM ''@abs_builddir@/results/copy1.data''';
57+
END;
58+
$$;
59+
60+
SELECT * FROM copy1 ORDER BY 1;
61+
62+
DROP TABLE copy1;
Lines changed: 89 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,89 @@
1+
CREATE TABLE copy1 (a int, b float);
2+
-- COPY TO/FROM not authorized from client.
3+
DO LANGUAGE plpgsql $$
4+
BEGIN
5+
COPY copy1 TO stdout;
6+
END;
7+
$$;
8+
ERROR: cannot COPY to/from client in PL/pgSQL
9+
CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement
10+
DO LANGUAGE plpgsql $$
11+
BEGIN
12+
COPY copy1 FROM stdin;
13+
END;
14+
$$;
15+
ERROR: cannot COPY to/from client in PL/pgSQL
16+
CONTEXT: PL/pgSQL function inline_code_block line 3 at SQL statement
17+
DO LANGUAGE plpgsql $$
18+
BEGIN
19+
EXECUTE 'COPY copy1 TO stdout';
20+
END;
21+
$$;
22+
ERROR: cannot COPY to/from client in PL/pgSQL
23+
CONTEXT: PL/pgSQL function inline_code_block line 3 at EXECUTE
24+
DO LANGUAGE plpgsql $$
25+
BEGIN
26+
EXECUTE 'COPY copy1 FROM stdin';
27+
END;
28+
$$;
29+
ERROR: cannot COPY to/from client in PL/pgSQL
30+
CONTEXT: PL/pgSQL function inline_code_block line 3 at EXECUTE
31+
-- Valid cases
32+
-- COPY FROM
33+
DO LANGUAGE plpgsql $$
34+
BEGIN
35+
COPY copy1 FROM '@abs_builddir@/data/copy1.data';
36+
END;
37+
$$;
38+
SELECT * FROM copy1 ORDER BY 1;
39+
a | b
40+
---+-----
41+
1 | 1.1
42+
2 | 2.2
43+
3 | 3.3
44+
(3 rows)
45+
46+
TRUNCATE copy1;
47+
DO LANGUAGE plpgsql $$
48+
BEGIN
49+
EXECUTE 'COPY copy1 FROM ''@abs_builddir@/data/copy1.data''';
50+
END;
51+
$$;
52+
SELECT * FROM copy1 ORDER BY 1;
53+
a | b
54+
---+-----
55+
1 | 1.1
56+
2 | 2.2
57+
3 | 3.3
58+
(3 rows)
59+
60+
-- COPY TO
61+
-- Copy the data externally once, then process it back to the table.
62+
DO LANGUAGE plpgsql $$
63+
BEGIN
64+
COPY copy1 TO '@abs_builddir@/results/copy1.data';
65+
END;
66+
$$;
67+
TRUNCATE copy1;
68+
DO LANGUAGE plpgsql $$
69+
BEGIN
70+
COPY copy1 FROM '@abs_builddir@/results/copy1.data';
71+
END;
72+
$$;
73+
DO LANGUAGE plpgsql $$
74+
BEGIN
75+
EXECUTE 'COPY copy1 FROM ''@abs_builddir@/results/copy1.data''';
76+
END;
77+
$$;
78+
SELECT * FROM copy1 ORDER BY 1;
79+
a | b
80+
---+-----
81+
1 | 1.1
82+
1 | 1.1
83+
2 | 2.2
84+
2 | 2.2
85+
3 | 3.3
86+
3 | 3.3
87+
(6 rows)
88+
89+
DROP TABLE copy1;

‎src/pl/plpgsql/src/sql/.gitignore

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
/plpgsql_copy.sql

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp