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 * FROM NAME_TBL;
30                               f1
31-----------------------------------------------------------------
32 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
33 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
34 asdfghjkl;
35 343f%2a
36 d34aaasdf
37
38 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
39(7 rows)
40
41SELECT c.f1 FROM NAME_TBL c WHERE c.f1 <> '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
42                               f1
43-----------------------------------------------------------------
44 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
45 asdfghjkl;
46 343f%2a
47 d34aaasdf
48
49(5 rows)
50
51SELECT c.f1 FROM NAME_TBL c WHERE c.f1 = '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
52                               f1
53-----------------------------------------------------------------
54 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
55 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
56(2 rows)
57
58SELECT c.f1 FROM NAME_TBL c WHERE c.f1 < '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
59 f1
60----
61
62(1 row)
63
64SELECT c.f1 FROM NAME_TBL c WHERE c.f1 <= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
65                               f1
66-----------------------------------------------------------------
67 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
68
69 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
70(3 rows)
71
72SELECT c.f1 FROM NAME_TBL c WHERE c.f1 > '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
73                               f1
74-----------------------------------------------------------------
75 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
76 asdfghjkl;
77 343f%2a
78 d34aaasdf
79(4 rows)
80
81SELECT c.f1 FROM NAME_TBL c WHERE c.f1 >= '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQR';
82                               f1
83-----------------------------------------------------------------
84 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
85 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
86 asdfghjkl;
87 343f%2a
88 d34aaasdf
89 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
90(6 rows)
91
92SELECT c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*';
93                               f1
94-----------------------------------------------------------------
95 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
96 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
97 asdfghjkl;
98 343f%2a
99 d34aaasdf
100
101 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
102(7 rows)
103
104SELECT c.f1 FROM NAME_TBL c WHERE c.f1 !~ '.*';
105 f1
106----
107(0 rows)
108
109SELECT c.f1 FROM NAME_TBL c WHERE c.f1 ~ '[0-9]';
110                               f1
111-----------------------------------------------------------------
112 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
113 1234567890abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopq
114 343f%2a
115 d34aaasdf
116 1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890ABCDEFGHIJKLMNOPQ
117(5 rows)
118
119SELECT c.f1 FROM NAME_TBL c WHERE c.f1 ~ '.*asdf.*';
120     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