DROP DATABASE plperlbug;
CREATE DATABASE plperlbug;
\c plperlbug

CREATE LANGUAGE plperlu;

CREATE TABLE t1 (
    fooid serial not null,
    foo character(3) NOT NULL,
    PRIMARY KEY (fooid),
    UNIQUE (foo)
);
CREATE TABLE t2 (
    foobarid serial not null,
    foobar1 character(3) NOT NULL,
    foobar2 character(3) NOT NULL,
    PRIMARY KEY (foobarid),
    UNIQUE (foobar1,foobar2)
);
CREATE TABLE t3 (
    foobarid integer NOT NULL,
    foobar4 timestamp with time zone NOT NULL,
    foo1 numeric NOT NULL,
    foo2 numeric NOT NULL,
    foo3 interval NOT NULL,
    PRIMARY KEY (foobarid),
    CONSTRAINT t3_foo1_check CHECK ((foo1 > (0)::numeric))
);

CREATE OR REPLACE FUNCTION func0001(_foobar1 character, _foobar2 character) RETURNS integer AS $BODY$
SELECT foobarid FROM t2 WHERE foobar1 = $1 AND foobar2 = $2
$BODY$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION func0005() RETURNS record LANGUAGE plperlu AS $BODY$
use strict;
use warnings;
die "die for fun";
$BODY$;


CREATE OR REPLACE FUNCTION func0002(
    OUT foobar4_ timestamp with time zone,
    OUT foo1_ numeric,
    OUT foo2_ numeric,
    _foobar1 character,
    _foobar2 character,
    _foobar3 timestamp with time zone
) RETURNS record AS $BODY$
DECLARE
_foobarid integer;
_foo3 interval;
BEGIN
_foobarid := func0001(_foobar1, _foobar2);
BEGIN
    RAISE DEBUG '% % %', 'a', 'b', 'c';
    PERFORM func0005();
EXCEPTION WHEN OTHERS THEN
    RAISE DEBUG '%', 'foo';
    SELECT foobar4, foo1, foo2, foo3 INTO foobar4_, foo1_, foo2_, _foo3 FROM t3 WHERE foobarid = 12345;
END;
RETURN;
END;
$BODY$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION func0003() RETURNS boolean AS $FUNC$
use strict;
use warnings;
my $prepared = spi_prepare('SELECT * FROM func0002($1,$2,NULL)', 'VARCHAR','VARCHAR');
my $a = spi_exec_prepared($prepared,'AAA','BBB')->{rows}->[0]->{fx};
my $b = spi_exec_prepared($prepared,'AAA','BBB')->{rows}->[0]->{fx};
return 1;
$FUNC$ LANGUAGE plperlu;

SELECT func0003();
