1 -- suppress CONTEXT so that function OIDs aren't in output
3 -- Test composite-type arguments
4 select tcl_composite_arg_ref1(row('tkey', 42, 'ref2'));
6 ------------------------
10 select tcl_composite_arg_ref2(row('tkey', 42, 'ref2'));
11 tcl_composite_arg_ref2
12 ------------------------
16 -- More tests for composite argument/result types
17 create domain d_comp1 as T_comp1 check ((value).ref1 > 0);
18 create function tcl_record_arg(record, fldname text) returns int as '
21 select tcl_record_arg(row('tkey', 42, 'ref2')::T_comp1, 'ref1');
27 select tcl_record_arg(row('tkey', 42, 'ref2')::d_comp1, 'ref1');
33 select tcl_record_arg(row(2,4), 'f2');
39 create function tcl_cdomain_arg(d_comp1) returns int as '
42 select tcl_cdomain_arg(row('tkey', 42, 'ref2'));
48 select tcl_cdomain_arg(row('tkey', 42, 'ref2')::T_comp1);
54 select tcl_cdomain_arg(row('tkey', -1, 'ref2')); -- fail
55 ERROR: value for domain d_comp1 violates check constraint "d_comp1_check"
56 -- Test argisnull primitive
57 select tcl_argisnull('foo');
63 select tcl_argisnull('');
69 select tcl_argisnull(null);
75 -- test some error cases
76 create function tcl_error(out a int, out b int) as $$return {$$ language pltcl;
78 ERROR: missing close-brace
79 create function bad_record(out a text, out b text) as $$return [list a]$$ language pltcl;
81 ERROR: column name/value list must have even number of elements
82 create function bad_field(out a text, out b text) as $$return [list a 1 b 2 cow 3]$$ language pltcl;
84 ERROR: column name/value list contains nonexistent column name "cow"
85 -- test compound return
86 select * from tcl_test_cube_squared(5);
93 select * from tcl_test_squared_rows(0,5);
103 select * from tcl_test_sequence(0,5) as a;
113 select 1, tcl_test_sequence(0,5);
114 ?column? | tcl_test_sequence
115 ----------+-------------------
123 create function non_srf() returns int as $$return_next 1$$ language pltcl;
125 ERROR: return_next cannot be used in non-set-returning functions
126 create function bad_record_srf(out a text, out b text) returns setof record as $$
129 select bad_record_srf();
130 ERROR: column name/value list must have even number of elements
131 create function bad_field_srf(out a text, out b text) returns setof record as $$
132 return_next [list a 1 b 2 cow 3]
134 select bad_field_srf();
135 ERROR: column name/value list contains nonexistent column name "cow"
136 -- test composite and domain-over-composite results
137 create function tcl_composite_result(int) returns T_comp1 as $$
138 return [list tkey tkey1 ref1 $1 ref2 ref22]
140 select tcl_composite_result(1001);
142 --------------------------------------------
143 ("tkey1 ",1001,"ref22 ")
146 select * from tcl_composite_result(1002);
148 ------------+------+----------------------
152 create function tcl_dcomposite_result(int) returns d_comp1 as $$
153 return [list tkey tkey2 ref1 $1 ref2 ref42]
155 select tcl_dcomposite_result(1001);
156 tcl_dcomposite_result
157 --------------------------------------------
158 ("tkey2 ",1001,"ref42 ")
161 select * from tcl_dcomposite_result(1002);
163 ------------+------+----------------------
167 select * from tcl_dcomposite_result(-1); -- fail
168 ERROR: value for domain d_comp1 violates check constraint "d_comp1_check"
169 create function tcl_record_result(int) returns record as $$
170 return [list q1 sometext q2 $1 q3 moretext]
172 select tcl_record_result(42); -- fail
173 ERROR: function returning record called in context that cannot accept type record
174 select * from tcl_record_result(42); -- fail
175 ERROR: a column definition list is required for functions returning "record" at character 15
176 select * from tcl_record_result(42) as (q1 text, q2 int, q3 text);
178 ----------+----+----------
179 sometext | 42 | moretext
182 select * from tcl_record_result(42) as (q1 text, q2 int, q3 text, q4 int);
184 ----------+----+----------+----
185 sometext | 42 | moretext |
188 select * from tcl_record_result(42) as (q1 text, q2 int, q4 int); -- fail
189 ERROR: column name/value list contains nonexistent column name "q3"
191 select tcl_eval('quote foo bar');
192 ERROR: wrong # args: should be "quote string"
193 select tcl_eval('quote [format %c 39]');
199 select tcl_eval('quote [format %c 92]');
206 select tcl_eval('argisnull');
207 ERROR: wrong # args: should be "argisnull argno"
208 select tcl_eval('argisnull 14');
209 ERROR: argno out of range
210 select tcl_eval('argisnull abc');
211 ERROR: expected integer but got "abc"
213 select tcl_eval('return_null 14');
214 ERROR: wrong # args: should be "return_null "
216 select tcl_eval('spi_exec');
217 ERROR: wrong # args: should be "spi_exec ?-count n? ?-array name? query ?loop body?"
218 select tcl_eval('spi_exec -count');
219 ERROR: missing argument to -count or -array
220 select tcl_eval('spi_exec -array');
221 ERROR: missing argument to -count or -array
222 select tcl_eval('spi_exec -count abc');
223 ERROR: expected integer but got "abc"
224 select tcl_eval('spi_exec query loop body toomuch');
225 ERROR: wrong # args: should be "query ?loop body?"
226 select tcl_eval('spi_exec "begin; rollback;"');
227 ERROR: pltcl: SPI_execute failed: SPI_ERROR_TRANSACTION
229 select tcl_eval('spi_execp');
230 ERROR: missing argument to -count or -array
231 select tcl_eval('spi_execp -count');
232 ERROR: missing argument to -array, -count or -nulls
233 select tcl_eval('spi_execp -array');
234 ERROR: missing argument to -array, -count or -nulls
235 select tcl_eval('spi_execp -count abc');
236 ERROR: expected integer but got "abc"
237 select tcl_eval('spi_execp -nulls');
238 ERROR: missing argument to -array, -count or -nulls
239 select tcl_eval('spi_execp ""');
240 ERROR: invalid queryid ''
242 select tcl_eval('spi_prepare');
243 ERROR: wrong # args: should be "spi_prepare query argtypes"
244 select tcl_eval('spi_prepare a b');
245 ERROR: type "b" does not exist
246 select tcl_eval('spi_prepare a "b {"');
247 ERROR: unmatched open brace in list
248 select tcl_error_handling_test($tcl$spi_prepare "select moo" []$tcl$);
249 tcl_error_handling_test
250 --------------------------------------
252 condition: undefined_column +
254 message: column "moo" does not exist+
255 statement: select moo
258 -- test full error text
259 select tcl_error_handling_test($tcl$
263 USING HINT = 'my hint'
264 , DETAIL = 'my detail'
265 , SCHEMA = 'my schema'
267 , COLUMN = 'my column'
268 , CONSTRAINT = 'my constraint'
269 , DATATYPE = 'my datatype'
273 tcl_error_handling_test
274 --------------------------------------------------------------
277 condition: raise_exception +
278 constraint: my constraint +
279 context: PL/pgSQL function inline_code_block line 3 at RAISE+
280 SQL statement "DO $$ +
283 USING HINT = 'my hint' +
284 , DETAIL = 'my detail' +
285 , SCHEMA = 'my schema' +
286 , TABLE = 'my table' +
287 , COLUMN = 'my column' +
288 , CONSTRAINT = 'my constraint' +
289 , DATATYPE = 'my datatype' +
292 datatype: my datatype +
295 message: my message +
300 -- verify tcl_error_handling_test() properly reports non-postgres errors
301 select tcl_error_handling_test('moo');
302 tcl_error_handling_test
303 ----------------------------
304 invalid command name "moo"
308 select tcl_eval('elog');
309 ERROR: wrong # args: should be "elog level msg"
310 select tcl_eval('elog foo bar');
311 ERROR: bad priority "foo": must be DEBUG, LOG, INFO, NOTICE, WARNING, ERROR, or FATAL
313 select tcl_eval('error "forced error"');
315 -- test loop control in spi_exec[p]
316 select tcl_spi_exec(true, 'break');
317 NOTICE: col1 1, col2 foo
318 NOTICE: col1 2, col2 bar
319 NOTICE: action: break
320 NOTICE: end of function
326 select tcl_spi_exec(true, 'continue');
327 NOTICE: col1 1, col2 foo
328 NOTICE: col1 2, col2 bar
329 NOTICE: action: continue
330 NOTICE: col1 3, col2 baz
331 NOTICE: end of function
337 select tcl_spi_exec(true, 'error');
338 NOTICE: col1 1, col2 foo
339 NOTICE: col1 2, col2 bar
340 NOTICE: action: error
342 select tcl_spi_exec(true, 'return');
343 NOTICE: col1 1, col2 foo
344 NOTICE: col1 2, col2 bar
345 NOTICE: action: return
351 select tcl_spi_exec(false, 'break');
352 NOTICE: col1 1, col2 foo
353 NOTICE: col1 2, col2 bar
354 NOTICE: action: break
355 NOTICE: end of function
361 select tcl_spi_exec(false, 'continue');
362 NOTICE: col1 1, col2 foo
363 NOTICE: col1 2, col2 bar
364 NOTICE: action: continue
365 NOTICE: col1 3, col2 baz
366 NOTICE: end of function
372 select tcl_spi_exec(false, 'error');
373 NOTICE: col1 1, col2 foo
374 NOTICE: col1 2, col2 bar
375 NOTICE: action: error
377 select tcl_spi_exec(false, 'return');
378 NOTICE: col1 1, col2 foo
379 NOTICE: col1 2, col2 bar
380 NOTICE: action: return
386 -- forcibly run the Tcl event loop for awhile, to check that we have not
387 -- messed things up too badly by disabling the Tcl notifier subsystem
389 unset -nocomplain ::tcl_vwait
390 after 100 {set ::tcl_vwait 1}
392 unset -nocomplain ::tcl_vwait$$);