Home
> PostgreSQL > dbi-link: fix error on insert/update/delete into/from remote mysql table
dbi-link: fix error on insert/update/delete into/from remote mysql table
I have installed dbi-link version 2.0.0 into my PostgreSQL 8.4 environment. The purpose is to query or modify remote mysql table from within postgresql user-defined-function. And whenever I try to insert/update/delete record into/from remote mysql table, I consistently get error message:
ERROR: error from Perl function “shadow_trigger_func”: Can’t call method “quote” on an undefined value at line 61
So i go to the mentioned function and found out sub functions: make_pairs, do_insert, do_update and do_delete try to access variables: $data_source_id and $table which are declared locally in main function.
Knowing that problem, I have modified parameter passing as follows:
Within main function:
…
if ($iud->{ $_TD->{new}{iud_action} }) {
$iud->{ $_TD->{new}{iud_action} }->({
payload => $_TD->{new},
tbl => $table,
source_id => $data_source_id
});
}
else {
die “Trigger event was $_TD->{new}{iud_action}<, but should have been one of I, U or D!"
}
…
if ($iud->{ $_TD->{new}{iud_action} }) {
$iud->{ $_TD->{new}{iud_action} }->({
payload => $_TD->{new},
tbl => $table,
source_id => $data_source_id
});
}
else {
die “Trigger event was $_TD->{new}{iud_action}<, but should have been one of I, U or D!"
}
…
And inside sub functions: modify $table and $data_source_id into $params->{tbl} and $params->{source_id}, respectively.
Categories: PostgreSQL
Abdulyadi, nice.
Did you send patch to David so he can include it in next dbi-link release?
(BTW, in this post’s comments we can see a sneaky kind of SEO trackback spam)
Hello, works to insert but does not work to eliminate, get this message:
SQL Error: ERROR: error from Perl function “shadow_trigger_func”: Can’t call method “quote” on an undefined value at line 156.
Thanks.
Ok, my complete code is:
CREATE OR REPLACE FUNCTION dbi_link.shadow_trigger_func()
RETURNS trigger AS
$BODY$
require 5.8.3;
######################################################
# #
# Immediately reject anything that is not an INSERT. #
# #
######################################################
if ($_TD->{event} ne ‘INSERT’) {
return “SKIP”;
}
spi_exec_query(‘SELECT dbi_link.dbi_link_init()’);
my $data_source_id = shift;
die “In shadow_trigger_function, data_source_id must be an integer”
unless ($data_source_id =~ /^\d+$/);
my $query = “SELECT dbi_link.cache_connection( $data_source_id )”;
warn “In shadow_trigger_function, calling\n $query” if $_SHARED{debug};
warn “In shadow_trigger_function, the trigger payload is\n”. Dump(\$_TD) if $_SHARED{debug};
my $rv = spi_exec_query($query);
my $table = $_TD->{relname};
warn “Raw table name is $table”;
warn “In trigger on $table, action is $_TD->{new}{iud_action}” if $_SHARED{debug};
$table =~ s{
\A # Beginning of string.
(.*) # Actual table name.
_shadow # Strip off shadow.
\z # End of string.
}
{$1}sx;
warn “Cooked table name is $table”;
my $iud = {
I => \&do_insert,
U => \&do_update,
D => \&do_delete,
};
if ($iud->{ $_TD->{new}{iud_action} }) {
$iud->{ $_TD->{new}{iud_action} }->({
payload => $_TD->{new},
tbl => $table,
source_id => $data_source_id
});
}
else {
die “Trigger event was $_TD->{new}{iud_action}{payload});
die “In do_insert, payload must be a hash reference!”
unless (ref $params->{payload} eq ‘HASH’);
my (@keys, @values);
foreach my $key (sort keys %{ $params->{payload} } ) {
next unless $key =~ /^.?new_(.*)/;
my $real_key = $1;
push @keys, $real_key;
push @values, $_SHARED{dbh}{ $params->{source_id} }->quote(
$params->{payload}{$key}
);
}
my $sql = <{tbl} (
@{[
join(
",\n ",
@keys
)
]}
)
VALUES (
@{[
join(
",\n ",
@values
)
]}
)
SQL
warn “SQL is\n$sql” if $_SHARED{debug};
$_SHARED{dbh}{ $params->{source_id} }->do($sql);
}
sub do_update {
my ($params) = @_;
die “In do_update, must pass a payload!”
unless (defined $params->{payload});
die “In do_update, payload must be a hash reference!”
unless (ref $params->{payload} eq ‘HASH’);
my $sql = <{tbl}
SET
@{[ make_pairs({
payload => $params->{payload},
which => 'new',
joiner => ",\n ",
source_id => $params->{source_id}
}) ]}
WHERE
@{[ make_pairs({
payload => $params->{payload},
which => 'old',
joiner => "\nAND ",
transform_null => 'true',
source_id => $params->{source_id}
}) ]}
SQL
warn “SQL is\n$sql” if $_SHARED{debug};
$_SHARED{dbh}{ $params->{source_id} }->do($sql);
}
sub do_delete {
my ($params) = @_;
die “In do_delete, must pass a payload!”
unless (defined $params->{payload});
die “In do_delete, payload must be a hash reference!”
unless (ref $params->{payload} eq ‘HASH’);
my $sql = <{tbl}
WHERE
@{[ make_pairs({
payload => $params->{payload},
which => 'old',
joiner => "\nAND ",
transform_null => 'true',
source_id => $params->{source_id}
}) ]}
SQL
warn “SQL is\n$sql” if $_SHARED{debug};
$_SHARED{dbh}{ $params->{source_id} }->do($sql);
}
sub make_pairs {
my ($params) = @_;
die “In make_pairs, must pass a payload!”
unless (defined $params->{payload});
die “In make_pairs, payload must be a hash reference!”
unless (ref $params->{payload} eq ‘HASH’);
warn “In make_pairs, parameters are:\n”. Dump($params) if $_SHARED{debug};
my @pairs;
foreach my $key (
keys %{ $params->{payload} }
) {
next unless $key =~ m/^(.?)$params->{which}_(.*)/;
my $left = “$1$2″;
warn “In make_pairs, raw key is $key, cooked key is $left” if $_SHARED{debug};
if (
defined $params->{transform_null} && # In a WHERE clause,
!defined $params->{payload}{$key} # turn undef into IS NULL
) {
push @pairs, “$left IS NULL”;
}
else {
push @pairs, “$left = ” . $_SHARED{dbh}{ $params->{source_id} }->quote(
$params->{payload}{$key}
);
}
}
my $ret =
join (
$params->{joiner},
@pairs,
);
warn “In make_pairs, the pairs are:\n”. Dump(\@pairs) if $_SHARED{debug};
return $ret;
}
$BODY$
LANGUAGE plperlu VOLATILE;
I’m using Postgresql 8.3
CREATE OR REPLACE FUNCTION dbi_link.shadow_trigger_func()
RETURNS trigger AS
$$
require 5.8.3;
######################################################
# #
# Immediately reject anything that is not an INSERT. #
# #
######################################################
if ($_TD->{event} ne ‘INSERT’) {
return “SKIP”;
}
spi_exec_query(‘SELECT dbi_link.dbi_link_init()’);
my $data_source_id = shift;
die “In shadow_trigger_function, data_source_id must be an integer”
unless ($data_source_id =~ /^\d+$/);
my $query = “SELECT dbi_link.cache_connection( $data_source_id )”;
warn “In shadow_trigger_function, calling\n $query” if $_SHARED{debug};
warn “In shadow_trigger_function, the trigger payload is\n”. Dump(\$_TD) if $_SHARED{debug};
my $rv = spi_exec_query($query);
my $table = $_TD->{relname};
warn “Raw table name is $table”;
warn “In trigger on $table, action is $_TD->{new}{iud_action}” if $_SHARED{debug};
$table =~ s{
\A # Beginning of string.
(.*) # Actual table name.
_shadow # Strip off shadow.
\z # End of string.
}
{$1}sx;
warn “Cooked table name is $table”;
my $iud = {
I => \&do_insert,
U => \&do_update,
D => \&do_delete,
};
if ($iud->{ $_TD->{new}{iud_action} }) {
$iud->{ $_TD->{new}{iud_action} }->({
payload => $_TD->{new},
tbl => $table,
source_id => $data_source_id
});
}
else {
die “Trigger event was $_TD->{new}{iud_action}{payload});
die “In do_insert, payload must be a hash reference!”
unless (ref $params->{payload} eq ‘HASH’);
my (@keys, @values);
foreach my $key (sort keys %{ $params->{payload} } ) {
next unless $key =~ /^.?new_(.*)/;
my $real_key = $1;
push @keys, $real_key;
push @values, $_SHARED{dbh}{ $params->{source_id} }->quote(
$params->{payload}{$key}
);
}
my $sql = <{tbl} (
@{[
join(
",\n ",
@keys
)
]}
)
VALUES (
@{[
join(
",\n ",
@values
)
]}
)
SQL
warn “SQL is\n$sql” if $_SHARED{debug};
$_SHARED{dbh}{ $params->{source_id} }->do($sql);
}
sub do_update {
my ($params) = @_;
die “In do_update, must pass a payload!”
unless (defined $params->{payload});
die “In do_update, payload must be a hash reference!”
unless (ref $params->{payload} eq ‘HASH’);
my $sql = <{tbl}
SET
@{[ make_pairs({
payload => $params->{payload},
which => 'new',
joiner => ",\n ",
}) ]}
WHERE
@{[ make_pairs({
payload => $params->{payload},
which => 'old',
joiner => "\nAND ",
transform_null => 'true'
}) ]}
SQL
warn “SQL is\n$sql” if $_SHARED{debug};
$_SHARED{dbh}{ $params->{source_id} }->do($sql);
}
sub do_delete {
my ($params) = @_;
die “In do_delete, must pass a payload!”
unless (defined $params->{payload});
die “In do_delete, payload must be a hash reference!”
unless (ref $params->{payload} eq ‘HASH’);
my $sql = < $params->{payload},
which => ‘old’,
joiner => “\nAND “,
transform_null => ‘true’
}) ]}
SQL
warn “SQL is\n$sql” if $_SHARED{debug};
$_SHARED{dbh}{ $params->{source_id} }->do($sql);
}
sub make_pairs {
my ($params) = @_;
die “In make_pairs, must pass a payload!”
unless (defined $params->{payload});
die “In make_pairs, payload must be a hash reference!”
unless (ref $params->{payload} eq ‘HASH’);
warn “In make_pairs, parameters are:\n”. Dump($params) if $_SHARED{debug};
my @pairs;
foreach my $key (
keys %{ $params->{payload} }
) {
next unless $key =~ m/^(.?)$params->{which}_(.*)/;
my $left = “$1$2″;
warn “In make_pairs, raw key is $key, cooked key is $left” if $_SHARED{debug};
if (
defined $params->{transform_null} && # In a WHERE clause,
!defined $params->{payload}{$key} # turn undef into IS NULL
) {
push @pairs, “$left IS NULL”;
}
else {
push @pairs, “$left = ” . $_SHARED{dbh}{ $params->{source_id} }->quote(
$params->{payload}{$key}
);
}
}
my $ret =
join (
$params->{joiner},
@pairs,
);
warn “In make_pairs, the pairs are:\n”. Dump(\@pairs) if $_SHARED{debug};
return $ret;
}
$$
LANGUAGE ‘plperlu’
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;
Hi, I have found that you have missed some code lines containing WHERE clause in your do_delete function.
you were right thank you very much: D
sub do_delete {
my ($params) = @_;
die “In do_delete, must pass a payload!”
unless (defined $params->{payload});
die “In do_delete, payload must be a hash reference!”
unless (ref $params->{payload} eq ‘HASH’);
my $sql = <{tbl}
WHERE
@{[ make_pairs({
payload => $params->{payload},
which => 'old',
joiner => "\nAND ",
transform_null => 'true',
source_id => "$params->{source_id}"
}) ]}
SQL
warn “SQL is\n$sql” if $_SHARED{debug};
$_SHARED{dbh}{ $params->{source_id} }->do($sql);
}