2 -- Test result value processing
4 CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$
13 SELECT * FROM perl_int(42);
19 CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$
28 SELECT * FROM perl_int(42);
34 CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
37 SELECT perl_set_int(5);
42 SELECT * FROM perl_set_int(5);
47 CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
50 SELECT perl_set_int(5);
61 SELECT * FROM perl_set_int(5);
72 CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
73 CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
82 SELECT * FROM perl_row();
88 CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
89 return {f2 => 'hello', f1 => 1, f3 => 'world'};
97 SELECT * FROM perl_row();
103 CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
111 SELECT * FROM perl_set();
116 CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
118 { f1 => 1, f2 => 'Hello', f3 => 'World' },
120 { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
124 ERROR: SETOF-composite-returning PL/Perl function must call return_next with reference to hash
125 CONTEXT: PL/Perl function "perl_set"
126 SELECT * FROM perl_set();
127 ERROR: SETOF-composite-returning PL/Perl function must call return_next with reference to hash
128 CONTEXT: PL/Perl function "perl_set"
129 CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
131 { f1 => 1, f2 => 'Hello', f3 => 'World' },
132 { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
133 { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
138 ----------------------
144 SELECT * FROM perl_set();
146 ----+-------+------------
148 2 | Hello | PostgreSQL
152 CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$
155 SELECT perl_record();
161 SELECT * FROM perl_record();
162 ERROR: a column definition list is required for functions returning "record"
163 LINE 1: SELECT * FROM perl_record();
165 SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text);
171 CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$
172 return {f2 => 'hello', f1 => 1, f3 => 'world'};
174 SELECT perl_record();
175 ERROR: function returning record called in context that cannot accept type record
176 CONTEXT: PL/Perl function "perl_record"
177 SELECT * FROM perl_record();
178 ERROR: a column definition list is required for functions returning "record"
179 LINE 1: SELECT * FROM perl_record();
181 SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text);
187 CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$
190 SELECT perl_record_set();
191 ERROR: set-valued function called in context that cannot accept a set
192 CONTEXT: PL/Perl function "perl_record_set"
193 SELECT * FROM perl_record_set();
194 ERROR: a column definition list is required for functions returning "record"
195 LINE 1: SELECT * FROM perl_record_set();
197 SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text);
202 CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$
204 { f1 => 1, f2 => 'Hello', f3 => 'World' },
206 { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
209 SELECT perl_record_set();
210 ERROR: set-valued function called in context that cannot accept a set
211 CONTEXT: PL/Perl function "perl_record_set"
212 SELECT * FROM perl_record_set();
213 ERROR: a column definition list is required for functions returning "record"
214 LINE 1: SELECT * FROM perl_record_set();
216 SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text);
217 ERROR: SETOF-composite-returning PL/Perl function must call return_next with reference to hash
218 CONTEXT: PL/Perl function "perl_record_set"
219 CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$
221 { f1 => 1, f2 => 'Hello', f3 => 'World' },
222 { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
223 { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
226 SELECT perl_record_set();
227 ERROR: set-valued function called in context that cannot accept a set
228 CONTEXT: PL/Perl function "perl_record_set"
229 SELECT * FROM perl_record_set();
230 ERROR: a column definition list is required for functions returning "record"
231 LINE 1: SELECT * FROM perl_record_set();
233 SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text);
235 ----+-------+------------
237 2 | Hello | PostgreSQL
241 CREATE OR REPLACE FUNCTION
242 perl_out_params(f1 out integer, f2 out text, f3 out text) AS $$
243 return {f2 => 'hello', f1 => 1, f3 => 'world'};
245 SELECT perl_out_params();
251 SELECT * FROM perl_out_params();
257 SELECT (perl_out_params()).f2;
263 CREATE OR REPLACE FUNCTION
264 perl_out_params_set(out f1 integer, out f2 text, out f3 text)
265 RETURNS SETOF record AS $$
267 { f1 => 1, f2 => 'Hello', f3 => 'World' },
268 { f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
269 { f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
272 SELECT perl_out_params_set();
274 ----------------------
280 SELECT * FROM perl_out_params_set();
282 ----+-------+------------
284 2 | Hello | PostgreSQL
288 SELECT (perl_out_params_set()).f3;
297 -- Check behavior with erroneous return values
299 CREATE TYPE footype AS (x INTEGER, y INTEGER);
300 CREATE OR REPLACE FUNCTION foo_good() RETURNS SETOF footype AS $$
306 SELECT * FROM foo_good();
313 CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$
314 return {y => 3, z => 4};
316 SELECT * FROM foo_bad();
317 ERROR: Perl hash contains nonexistent column "z"
318 CONTEXT: PL/Perl function "foo_bad"
319 CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$
322 SELECT * FROM foo_bad();
323 ERROR: composite-returning PL/Perl function must return reference to hash
324 CONTEXT: PL/Perl function "foo_bad"
325 CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$
331 SELECT * FROM foo_bad();
332 ERROR: composite-returning PL/Perl function must return reference to hash
333 CONTEXT: PL/Perl function "foo_bad"
334 CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$
337 SELECT * FROM foo_set_bad();
338 ERROR: set-returning PL/Perl function must return reference to array or use return_next
339 CONTEXT: PL/Perl function "foo_set_bad"
340 CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$
341 return {y => 3, z => 4};
343 SELECT * FROM foo_set_bad();
344 ERROR: set-returning PL/Perl function must return reference to array or use return_next
345 CONTEXT: PL/Perl function "foo_set_bad"
346 CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$
352 SELECT * FROM foo_set_bad();
353 ERROR: SETOF-composite-returning PL/Perl function must call return_next with reference to hash
354 CONTEXT: PL/Perl function "foo_set_bad"
355 CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$
360 SELECT * FROM foo_set_bad();
361 ERROR: Perl hash contains nonexistent column "z"
362 CONTEXT: PL/Perl function "foo_set_bad"
364 -- Check passing a tuple argument
366 CREATE OR REPLACE FUNCTION perl_get_field(footype, text) RETURNS integer AS $$
367 return $_[0]->{$_[1]};
369 SELECT perl_get_field((11,12), 'x');
375 SELECT perl_get_field((11,12), 'y');
381 SELECT perl_get_field((11,12), 'z');
390 CREATE OR REPLACE FUNCTION perl_srf_rn() RETURNS SETOF RECORD AS $$
392 for ("World", "PostgreSQL", "PL/Perl") {
393 return_next({f1=>++$i, f2=>'Hello', f3=>$_});
397 SELECT * from perl_srf_rn() AS (f1 INTEGER, f2 TEXT, f3 TEXT);
399 ----+-------+------------
401 2 | Hello | PostgreSQL
406 -- Test spi_query/spi_fetchrow
408 CREATE OR REPLACE FUNCTION perl_spi_func() RETURNS SETOF INTEGER AS $$
409 my $x = spi_query("select 1 as a union select 2 as a");
410 while (defined (my $y = spi_fetchrow($x))) {
411 return_next($y->{a});
415 SELECT * from perl_spi_func();
423 -- Test spi_fetchrow abort
425 CREATE OR REPLACE FUNCTION perl_spi_func2() RETURNS INTEGER AS $$
426 my $x = spi_query("select 1 as a union select 2 as a");
427 spi_cursor_close( $x);
430 SELECT * from perl_spi_func2();
437 --- Test recursion via SPI
439 CREATE OR REPLACE FUNCTION recurse(i int) RETURNS SETOF TEXT LANGUAGE plperl
443 foreach my $x (1..$i)
445 return_next "hello $x";
450 my $cursor = spi_query("select * from recurse($z)");
451 while (defined(my $row = spi_fetchrow($cursor)))
453 return_next "recurse $i: $row->{recurse}";
459 SELECT * FROM recurse(2);
466 SELECT * FROM recurse(3);
477 --- Test arrary return
479 CREATE OR REPLACE FUNCTION array_of_text() RETURNS TEXT[][]
480 LANGUAGE plperl as $$
481 return [['a"b',undef,'c,d'],['e\\f',undef,'g']];
483 SELECT array_of_text();
485 ---------------------------------------
486 {{"a\"b",NULL,"c,d"},{"e\\f",NULL,g}}
490 -- Test spi_prepare/spi_exec_prepared/spi_freeplan
492 CREATE OR REPLACE FUNCTION perl_spi_prepared(INTEGER) RETURNS INTEGER AS $$
493 my $x = spi_prepare('select $1 AS a', 'INTEGER');
494 my $q = spi_exec_prepared( $x, $_[0] + 1);
496 return $q->{rows}->[0]->{a};
498 SELECT * from perl_spi_prepared(42);
505 -- Test spi_prepare/spi_query_prepared/spi_freeplan
507 CREATE OR REPLACE FUNCTION perl_spi_prepared_set(INTEGER, INTEGER) RETURNS SETOF INTEGER AS $$
508 my $x = spi_prepare('SELECT $1 AS a union select $2 as a', 'INT4', 'INT4');
509 my $q = spi_query_prepared( $x, 1+$_[0], 2+$_[1]);
510 while (defined (my $y = spi_fetchrow($q))) {
516 SELECT * from perl_spi_prepared_set(1,2);
517 perl_spi_prepared_set
518 -----------------------
524 -- Test prepare with a type with spaces
526 CREATE OR REPLACE FUNCTION perl_spi_prepared_double(double precision) RETURNS double precision AS $$
527 my $x = spi_prepare('SELECT 10.0 * $1 AS a', 'DOUBLE PRECISION');
528 my $q = spi_query_prepared($x,$_[0]);
530 while (defined (my $y = spi_fetchrow($q))) {
536 SELECT perl_spi_prepared_double(4.35) as "double precision";
543 -- Test with a bad type
545 CREATE OR REPLACE FUNCTION perl_spi_prepared_bad(double precision) RETURNS double precision AS $$
546 my $x = spi_prepare('SELECT 10.0 * $1 AS a', 'does_not_exist');
547 my $q = spi_query_prepared($x,$_[0]);
549 while (defined (my $y = spi_fetchrow($q))) {
555 SELECT perl_spi_prepared_bad(4.35) as "double precision";
556 ERROR: type "does_not_exist" does not exist at line 2.
557 CONTEXT: PL/Perl function "perl_spi_prepared_bad"
558 -- simple test of a DO block
560 $a = 'This is a test';
563 NOTICE: This is a test
564 CONTEXT: PL/Perl anonymous code block
565 -- check that restricted operations are rejected in a plperl DO block
566 DO $$ eval "1+1"; $$ LANGUAGE plperl;
567 ERROR: 'eval "string"' trapped by operation mask at line 1.
568 CONTEXT: PL/Perl anonymous code block
569 -- check that we can't "use" a module that's not been loaded already
570 -- compile-time error: "Unable to load blib.pm into plperl"
571 DO $$ use blib; $$ LANGUAGE plperl;
572 ERROR: Unable to load blib.pm into plperl at line 1.
573 BEGIN failed--compilation aborted at line 1.
574 CONTEXT: PL/Perl anonymous code block
575 -- check that we can "use" a module that has already been loaded
576 -- runtime error: "Can't use string ("foo") as a SCALAR ref while "strict refs" in use
577 DO $do$ use strict; my $name = "foo"; my $ref = $$name; $do$ LANGUAGE plperl;
578 ERROR: Can't use string ("foo") as a SCALAR ref while "strict refs" in use at line 1.
579 CONTEXT: PL/Perl anonymous code block
580 -- check that we can "use warnings" (in this case to turn a warn into an error)
581 -- yields "ERROR: Useless use of length in void context"
582 DO $do$ use warnings FATAL => qw(void) ; length "abc" ; 1; $do$ LANGUAGE plperl;
583 ERROR: Useless use of length in void context at line 1.
584 CONTEXT: PL/Perl anonymous code block