trouble with on insert rule via libpg-perl

Started by Ron Petersonover 21 years ago3 messagesgeneral
Jump to latest
#1Ron Peterson
rpeterso@mtholyoke.edu

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

#2Ron Peterson
rpeterso@mtholyoke.edu
In reply to: Ron Peterson (#1)
Re: trouble with on insert rule via libpg-perl

On Fri, Dec 10, 2004 at 11:26:09PM -0500, rpeterso wrote:

I should have mentioned...

PostgreSQL versions 8.0.0beta5 and 7.4.6
libpq-perl 2.0.2
perl 5.8.4

--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

#3Ron Peterson
rpeterso@mtholyoke.edu
In reply to: Ron Peterson (#1)
Re: trouble with on insert rule via libpg-perl

On Fri, Dec 10, 2004 at 11:26:09PM -0500, Ron Peterson wrote:

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.

Fixed. I didn't set the schema search path properly in my perl code.

--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso