trouble with on insert rule via libpg-perl

From: Ron Peterson <rpeterso(at)mtholyoke(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: trouble with on insert rule via libpg-perl
Date: 2004-12-11 04:26:09
Message-ID: 20041211042609.GB13813@mtholyoke.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Peterson 2004-12-11 04:28:47 Re: trouble with on insert rule via libpg-perl
Previous Message Christopher Browne 2004-12-11 04:25:31 Re: any body able to access news.postgresql.org recently?