forked from nextgres/uplpgsql
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathplpgsql_copy.sql
More file actions
58 lines (50 loc) · 1.36 KB
/
Copy pathplpgsql_copy.sql
File metadata and controls
58 lines (50 loc) · 1.36 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- directory paths are passed to us in environment variables
\getenv abs_srcdir PG_ABS_SRCDIR
\getenv abs_builddir PG_ABS_BUILDDIR
-- set up file names to use
\set srcfilename :abs_srcdir '/data/copy1.data'
\set destfilename :abs_builddir '/results/copy1.data'
CREATE TABLE copy1 (a int, b float);
-- COPY TO/FROM not authorized from client.
DO LANGUAGE uplpgsql $$
BEGIN
COPY copy1 TO stdout;
END;
$$;
DO LANGUAGE uplpgsql $$
BEGIN
COPY copy1 FROM stdin;
END;
$$;
DO LANGUAGE uplpgsql $$
BEGIN
EXECUTE 'COPY copy1 TO stdout';
END;
$$;
DO LANGUAGE uplpgsql $$
BEGIN
EXECUTE 'COPY copy1 FROM stdin';
END;
$$;
-- Valid cases
-- COPY FROM
\set dobody 'BEGIN COPY copy1 FROM ' :'srcfilename' '; END'
DO LANGUAGE uplpgsql :'dobody';
SELECT * FROM copy1 ORDER BY 1;
TRUNCATE copy1;
\set cmd 'COPY copy1 FROM ' :'srcfilename'
\set dobody 'BEGIN EXECUTE ' :'cmd' '; END'
DO LANGUAGE uplpgsql :'dobody';
SELECT * FROM copy1 ORDER BY 1;
-- COPY TO
-- Copy the data externally once, then process it back to the table.
\set dobody 'BEGIN COPY copy1 TO ' :'destfilename' '; END'
DO LANGUAGE uplpgsql :'dobody';
TRUNCATE copy1;
\set dobody 'BEGIN COPY copy1 FROM ' :'destfilename' '; END'
DO LANGUAGE uplpgsql :'dobody';
\set cmd 'COPY copy1 FROM ' :'destfilename'
\set dobody 'BEGIN EXECUTE ' :'cmd' '; END'
DO LANGUAGE uplpgsql :'dobody';
SELECT * FROM copy1 ORDER BY 1;
DROP TABLE copy1;