I have a simple table, a view, and an on insert rule. These work fine,
ordinarily. But when I attempt to to insert a value into thesis_ps_v
via libpq_sql, nothing happens. If I capture the sql string I'm sending
(as per code sample below), print it to a file, and paste it into a psql
session, it works fine. If I use the same perl code (with the minor
required modifications to the sql string) to insert rows directly into
the the table, that works fine also. I've included the relevant tables
and perl code below.
######################################################################
CREATE TABLE thesis (
thesis_sha1
BYTEA
NOT NULL,
ps
BYTEA
NOT NULL,
id
INTEGER
DEFAULT nextval( 'thesis_id_seq' )
PRIMARY KEY
);
CREATE UNIQUE INDEX
thesis__sha1_ndx
ON
thesis( thesis_sha1 );
CREATE VIEW
thesis_ps_v
AS
SELECT
ps
FROM
thesis;
CREATE RULE thesis_ps_v_insert AS
ON INSERT TO thesis_ps_v
DO INSTEAD
INSERT INTO thesis ( thesis_sha1, ps )
VALUES ( digest( new.ps, 'sha1' ), new.ps );
######################################################################
sub upload_val ($) {
my( $octstr_ref ) = @_;
my $sqlstr = <<EOSQL;
INSERT INTO
thesis.thesis_ps_v( ps )
VALUES
( \'$$octstr_ref\' );
EOSQL
open( TEST, ">/secure/tmpdir/test.out" );
print TEST "\n\n$sqlstr\n\n";
close( TEST );
my $result = $CONN->exec( $sqlstr );
my $status = $result->resultStatus;
my $oid = $result->cmdTuples;
if( $CONN->errorMessage &&
! ( $oid eq "" || $status eq PGRES_COMMAND_OK ) ) {
print STDERR sprintf( 'SQL exec failed: %s',
$CONN->errorMessage ), "\n";
}
}
--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso
