1--
2-- NAME
3-- all inputs are silently truncated at NAMEDATALEN-1 (63) characters
4--
5-- fixed-length by reference
6SELECT name 'name string' = name 'name string' AS "True";
7 True
8------
9 t
10(1 row)
11
12SELECT name 'name string' = name 'name string ' AS "False";
13 False
14-------
15 f
16(1 row)
17
18--
19--
20--
21CREATE TABLE NAME_TBL(f1 name);
22INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR');
23INSERT INTO NAME_TBL(f1) VALUES ('1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqr');
24INSERT INTO NAME_TBL(f1) VALUES ('asdfghjkl;');
25INSERT INTO NAME_TBL(f1) VALUES ('343f%2a');
26INSERT INTO NAME_TBL(f1) VALUES ('d34aaasdf');
27INSERT INTO NAME_TBL(f1) VALUES ('');
28INSERT INTO NAME_TBL(f1) VALUES ('1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ');
29SELECT '' AS seven, * FROM NAME_TBL;
30 seven |                               f1
31-------+-----------------------------------------------------------------
32       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
33       | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
34       | asdfghjkl;
35       | 343f%2a
36       | d34aaasdf
37       |
38       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
39(7 rows)
40
41SELECT '' AS six, c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
42 six |                               f1
43-----+-----------------------------------------------------------------
44     | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
45     | asdfghjkl;
46     | 343f%2a
47     | d34aaasdf
48     |
49(5 rows)
50
51SELECT '' AS one, c.f1 FROM NAME_TBL c WHERE c.f1 = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
52 one |                               f1
53-----+-----------------------------------------------------------------
54     | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
55     | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
56(2 rows)
57
58SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 < '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
59 three | f1
60-------+----
61       |
62(1 row)
63
64SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
65 four |                               f1
66------+-----------------------------------------------------------------
67      | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
68      |
69      | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
70(3 rows)
71
72SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
73 three |                               f1
74-------+-----------------------------------------------------------------
75       | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
76       | asdfghjkl;
77       | 343f%2a
78       | d34aaasdf
79(4 rows)
80
81SELECT '' AS four, c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
82 four |                               f1
83------+-----------------------------------------------------------------
84      | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
85      | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
86      | asdfghjkl;
87      | 343f%2a
88      | d34aaasdf
89      | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
90(6 rows)
91
92SELECT '' AS seven, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*';
93 seven |                               f1
94-------+-----------------------------------------------------------------
95       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
96       | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
97       | asdfghjkl;
98       | 343f%2a
99       | d34aaasdf
100       |
101       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
102(7 rows)
103
104SELECT '' AS zero, c.f1 FROM NAME_TBL c WHERE c.f1 !~ '.*';
105 zero | f1
106------+----
107(0 rows)
108
109SELECT '' AS three, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]';
110 three |                               f1
111-------+-----------------------------------------------------------------
112       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
113       | 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
114       | 343f%2a
115       | d34aaasdf
116       | 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
117(5 rows)
118
119SELECT '' AS two, c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*asdf.*';
120 two |     f1
121-----+------------
122     | asdfghjkl;
123     | d34aaasdf
124(2 rows)
125
126DROP TABLE NAME_TBL;
127DO $$
128DECLARE r text[];
129BEGIN
130  r := parse_ident('Schemax.Tabley');
131  RAISE NOTICE '%', format('%I.%I', r[1], r[2]);
132  r := parse_ident('"SchemaX"."TableY"');
133  RAISE NOTICE '%', format('%I.%I', r[1], r[2]);
134END;
135$$;
136NOTICE:  schemax.tabley
137NOTICE:  "SchemaX"."TableY"
138SELECT parse_ident('foo.boo');
139 parse_ident
140-------------
141 {foo,boo}
142(1 row)
143
144SELECT parse_ident('foo.boo[]'); -- should fail
145ERROR:  string is not a valid identifier: "foo.boo[]"
146SELECT parse_ident('foo.boo[]', strict => false); -- ok
147 parse_ident
148-------------
149 {foo,boo}
150(1 row)
151
152-- should fail
153SELECT parse_ident(' ');
154ERROR:  string is not a valid identifier: " "
155SELECT parse_ident(' .aaa');
156ERROR:  string is not a valid identifier: " .aaa"
157DETAIL:  No valid identifier before ".".
158SELECT parse_ident(' aaa . ');
159ERROR:  string is not a valid identifier: " aaa . "
160DETAIL:  No valid identifier after ".".
161SELECT parse_ident('aaa.a%b');
162ERROR:  string is not a valid identifier: "aaa.a%b"
163SELECT parse_ident(E'X\rXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
164ERROR:  string is not a valid identifier: "X
165XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
166SELECT length(a[1]), length(a[2]) from parse_ident('"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx".yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy') as a ;
167 length | length
168--------+--------
169    414 |    289
170(1 row)
171
172SELECT parse_ident(' first . "  second  " ."   third   ". "  ' || repeat('x',66) || '"');
173                                                parse_ident
174-----------------------------------------------------------------------------------------------------------
175 {first,"  second  ","   third   ","  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}
176(1 row)
177
178SELECT parse_ident(' first . "  second  " ."   third   ". "  ' || repeat('x',66) || '"')::name[];
179                                             parse_ident
180------------------------------------------------------------------------------------------------------
181 {first,"  second  ","   third   ","  xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"}
182(1 row)
183
184SELECT parse_ident(E'"c".X XXXX\002XXXXXX');
185ERROR:  string is not a valid identifier: ""c".X XXXXXXXXXX"
186SELECT parse_ident('1020');
187ERROR:  string is not a valid identifier: "1020"
188SELECT parse_ident('10.20');
189ERROR:  string is not a valid identifier: "10.20"
190SELECT parse_ident('.');
191ERROR:  string is not a valid identifier: "."
192DETAIL:  No valid identifier before ".".
193SELECT parse_ident('.1020');
194ERROR:  string is not a valid identifier: ".1020"
195DETAIL:  No valid identifier before ".".
196SELECT parse_ident('xxx.1020');
197ERROR:  string is not a valid identifier: "xxx.1020"
198DETAIL:  No valid identifier after ".".
199