2020-03-06 17:11:23 -05:00
|
|
|
CREATE EXTENSION bool_plperl CASCADE;
|
|
|
|
|
NOTICE: installing required extension "plperl"
|
|
|
|
|
--- test transforming from perl
|
|
|
|
|
CREATE FUNCTION perl2int(int) RETURNS bool
|
|
|
|
|
LANGUAGE plperl
|
|
|
|
|
TRANSFORM FOR TYPE bool
|
|
|
|
|
AS $$
|
|
|
|
|
return shift;
|
|
|
|
|
$$;
|
|
|
|
|
CREATE FUNCTION perl2text(text) RETURNS bool
|
|
|
|
|
LANGUAGE plperl
|
|
|
|
|
TRANSFORM FOR TYPE bool
|
|
|
|
|
AS $$
|
|
|
|
|
return shift;
|
|
|
|
|
$$;
|
|
|
|
|
CREATE FUNCTION perl2undef() RETURNS bool
|
|
|
|
|
LANGUAGE plperl
|
|
|
|
|
TRANSFORM FOR TYPE bool
|
|
|
|
|
AS $$
|
|
|
|
|
return undef;
|
|
|
|
|
$$;
|
|
|
|
|
SELECT perl2int(1);
|
|
|
|
|
perl2int
|
|
|
|
|
----------
|
|
|
|
|
t
|
|
|
|
|
(1 row)
|
|
|
|
|
|
|
|
|
|
SELECT perl2int(0);
|
|
|
|
|
perl2int
|
|
|
|
|
----------
|
|
|
|
|
f
|
|
|
|
|
(1 row)
|
|
|
|
|
|
|
|
|
|
SELECT perl2text('foo');
|
|
|
|
|
perl2text
|
|
|
|
|
-----------
|
|
|
|
|
t
|
|
|
|
|
(1 row)
|
|
|
|
|
|
|
|
|
|
SELECT perl2text('');
|
|
|
|
|
perl2text
|
|
|
|
|
-----------
|
|
|
|
|
f
|
|
|
|
|
(1 row)
|
|
|
|
|
|
|
|
|
|
SELECT perl2undef() IS NULL AS p;
|
|
|
|
|
p
|
|
|
|
|
---
|
|
|
|
|
t
|
|
|
|
|
(1 row)
|
|
|
|
|
|
|
|
|
|
--- test transforming to perl
|
|
|
|
|
CREATE FUNCTION bool2perl(bool, bool, bool) RETURNS void
|
|
|
|
|
LANGUAGE plperl
|
2021-01-25 13:03:11 -05:00
|
|
|
TRANSFORM FOR TYPE bool, for type boolean -- duplicate to test ruleutils
|
2020-03-06 17:11:23 -05:00
|
|
|
AS $$
|
|
|
|
|
my ($x, $y, $z) = @_;
|
|
|
|
|
|
|
|
|
|
die("NULL mistransformed") if (defined($z));
|
|
|
|
|
die("TRUE mistransformed to UNDEF") if (!defined($x));
|
|
|
|
|
die("FALSE mistransformed to UNDEF") if (!defined($y));
|
|
|
|
|
die("TRUE mistransformed") if (!$x);
|
|
|
|
|
die("FALSE mistransformed") if ($y);
|
|
|
|
|
$$;
|
|
|
|
|
SELECT bool2perl (true, false, NULL);
|
|
|
|
|
bool2perl
|
|
|
|
|
-----------
|
|
|
|
|
|
|
|
|
|
(1 row)
|
|
|
|
|
|
2021-01-25 13:03:11 -05:00
|
|
|
--- test ruleutils
|
|
|
|
|
\sf bool2perl
|
|
|
|
|
CREATE OR REPLACE FUNCTION public.bool2perl(boolean, boolean, boolean)
|
|
|
|
|
RETURNS void
|
|
|
|
|
TRANSFORM FOR TYPE boolean, FOR TYPE boolean
|
|
|
|
|
LANGUAGE plperl
|
|
|
|
|
AS $function$
|
|
|
|
|
my ($x, $y, $z) = @_;
|
|
|
|
|
|
|
|
|
|
die("NULL mistransformed") if (defined($z));
|
|
|
|
|
die("TRUE mistransformed to UNDEF") if (!defined($x));
|
|
|
|
|
die("FALSE mistransformed to UNDEF") if (!defined($y));
|
|
|
|
|
die("TRUE mistransformed") if (!$x);
|
|
|
|
|
die("FALSE mistransformed") if ($y);
|
|
|
|
|
$function$
|
2020-03-06 17:11:23 -05:00
|
|
|
--- test selecting bool through SPI
|
|
|
|
|
CREATE FUNCTION spi_test() RETURNS void
|
|
|
|
|
LANGUAGE plperl
|
|
|
|
|
TRANSFORM FOR TYPE bool
|
|
|
|
|
AS $$
|
|
|
|
|
my $rv = spi_exec_query('SELECT true t, false f, NULL n')->{rows}->[0];
|
|
|
|
|
|
|
|
|
|
die("TRUE mistransformed to UNDEF in SPI") if (!defined ($rv->{t}));
|
|
|
|
|
die("FALSE mistransformed to UNDEF in SPI") if (!defined ($rv->{f}));
|
|
|
|
|
die("NULL mistransformed in SPI") if (defined ($rv->{n}));
|
|
|
|
|
die("TRUE mistransformed in SPI") if (!$rv->{t});
|
|
|
|
|
die("FALSE mistransformed in SPI") if ($rv->{f});
|
|
|
|
|
$$;
|
|
|
|
|
SELECT spi_test();
|
|
|
|
|
spi_test
|
|
|
|
|
----------
|
|
|
|
|
|
|
|
|
|
(1 row)
|
|
|
|
|
|
|
|
|
|
DROP EXTENSION plperl CASCADE;
|
|
|
|
|
NOTICE: drop cascades to 6 other objects
|
Fix erroneous construction of functions' dependencies on transforms.
The list of transform objects that a function should use is specified
in CREATE FUNCTION's TRANSFORM clause, and then represented indirectly
in pg_proc.protrftypes. However, ProcedureCreate completely ignored
that for purposes of constructing pg_depend entries, and instead made
the function depend on any transforms that exist for its parameter or
return data types. This is bad in both directions: the function could
be made dependent on a transform it does not actually use, or it
could try to use a transform that's since been dropped. (The latter
scenario would require use of a transform that's not for any of the
parameter or return types, but that seems legit for cases where the
function performs SQL operations internally.)
To fix, pass in the list of transform objects that CreateFunction
identified, and build pg_depend entries from that not from the
parameter/return types. This results in changes in the expected
test outputs in contrib/bool_plperl, which I guess are due to
different ordering of pg_depend entries -- that test case is
surely not exercising either of the problem scenarios.
This fix is not back-patchable as-is: changing the signature of
ProcedureCreate seems too risky in stable branches. We could
do something like making ProcedureCreate a wrapper around
ProcedureCreateExt or so. However, I'm more inclined to do
nothing in the back branches. We had no field complaints up to
now, so the hazards don't seem to be a big issue in practice.
And we couldn't do anything about existing pg_depend entries,
so a back-patched fix would result in a mishmash of dependencies
created according to different rules. That cure could be worse
than the disease, perhaps.
I bumped catversion just to lay down a marker that the expected
contents of pg_depend are a bit different than before.
Reported-by: Chapman Flack <jcflack@acm.org>
Author: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/3112950.1743984111@sss.pgh.pa.us
2025-04-07 13:31:27 -04:00
|
|
|
DETAIL: drop cascades to extension bool_plperl
|
2020-03-06 17:11:23 -05:00
|
|
|
drop cascades to function perl2int(integer)
|
|
|
|
|
drop cascades to function perl2text(text)
|
|
|
|
|
drop cascades to function perl2undef()
|
|
|
|
|
drop cascades to function bool2perl(boolean,boolean,boolean)
|
Fix erroneous construction of functions' dependencies on transforms.
The list of transform objects that a function should use is specified
in CREATE FUNCTION's TRANSFORM clause, and then represented indirectly
in pg_proc.protrftypes. However, ProcedureCreate completely ignored
that for purposes of constructing pg_depend entries, and instead made
the function depend on any transforms that exist for its parameter or
return data types. This is bad in both directions: the function could
be made dependent on a transform it does not actually use, or it
could try to use a transform that's since been dropped. (The latter
scenario would require use of a transform that's not for any of the
parameter or return types, but that seems legit for cases where the
function performs SQL operations internally.)
To fix, pass in the list of transform objects that CreateFunction
identified, and build pg_depend entries from that not from the
parameter/return types. This results in changes in the expected
test outputs in contrib/bool_plperl, which I guess are due to
different ordering of pg_depend entries -- that test case is
surely not exercising either of the problem scenarios.
This fix is not back-patchable as-is: changing the signature of
ProcedureCreate seems too risky in stable branches. We could
do something like making ProcedureCreate a wrapper around
ProcedureCreateExt or so. However, I'm more inclined to do
nothing in the back branches. We had no field complaints up to
now, so the hazards don't seem to be a big issue in practice.
And we couldn't do anything about existing pg_depend entries,
so a back-patched fix would result in a mishmash of dependencies
created according to different rules. That cure could be worse
than the disease, perhaps.
I bumped catversion just to lay down a marker that the expected
contents of pg_depend are a bit different than before.
Reported-by: Chapman Flack <jcflack@acm.org>
Author: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/3112950.1743984111@sss.pgh.pa.us
2025-04-07 13:31:27 -04:00
|
|
|
drop cascades to function spi_test()
|