Updating views : cannot figure out what goes wrong - Mailing list pgsql-novice

From Admin
Subject Updating views : cannot figure out what goes wrong
Date
Msg-id 000a01c4f419$d61e0d70$5497fea9@dirkspc
Whole thread Raw
List pgsql-novice
 Hi there,

First of all i'm using the postgresql 8.0rc2 on windows

I have a problem updating views and seem to be stuck.
My front-end to postgresql is ms-access and i have the following code in my VBA

strSql = "CREATE VIEW bondetailtmp AS SELECT * FROM bondetail WHERE subbonnr=" & Forms!bonnentest!bonnr & ";"
cn.Execute strSql
strSql = "CREATE RULE bondetailtmpupd AS ON UPDATE TO bondetailtmp " & _
        "DO INSTEAD NOTHING;"
cn.Execute strSql
strSql = "CREATE RULE bondetailtmpupd2 AS ON UPDATE TO bondetailtmp " & _
        "WHERE OLD.subbonbeschrijving <> NEW.subbonbeschrijving " & _
        "DO (UPDATE bondetail " & _
        "SET subbonbeschrijving=NEW.subbonbeschrijving);"
cn.Execute strSql
Forms!bonnentest.bondetailtmp.Form.RecordSource = "bondetailtmp"

So the recordsource for the form is set through a link I made to the bondetailtmp view.
Now when I try to edit the date in the form and move to another record then I get :
476 2005-01-06 18:46:34ERROR:  cannot update a view

476 2005-01-06 18:46:34HINT:  You need an unconditional ON UPDATE DO INSTEAD rule.

476 2005-01-06 18:46:34STATEMENT:  BEGIN;UPDATE "public"."bondetailtmp" SET "subbonbeschrijving"='Test 1000a'  WHERE "id" = 2417 AND "subbonnr" = '4'::float4 AND "subbonlijnnr" = '410'::float4 AND "subbongroep" = ' ' AND "subbonartikelnr1" = ' 15' AND "subbonartikelnr2" = ' ' AND "subboneenheid1" = 'st' AND "subboneenheid2" = ' ' AND "subbonaantal" = '10'::float4 AND "subbonaantaltot" = '10'::float4 AND "subbonaantal2" = '1'::float4 AND "subbonaantal3" = '1'::float4 AND "kleurcode" IS NULL AND "subbonprijsbf" = '110'::float4 AND "subbonprijseuro" = '0'::float4 AND "subbontotaalbf" = '990'::float4 AND "subbontotaaleuro" = '0'::float4 AND "berekend" IS NULL AND "berekeningen" IS NULL AND "munt1" = 'Euro' AND "munt2" IS NULL AND "subtotaaldetail" = 410 AND "subtotaal" = 0 AND "subtot" = 1 AND "btw" = '21'::float4 AND "tekening" = ' ' AND "stock" = ' ' AND "produktnr" = '0'::float4 AND "produktid" = 0 AND "voorraadid" = '0'::float4 AND "rendementuur" = '0'::float4 AND "totaaluren" = '0'::float4 AND "uur" = '0'::float4 AND "mat" = '0'::float4 AND "matuur" = '0'::float4 AND "subbonprijsbfaankoopuur" = '0'::float4 AND "subbonprijsbfaankoopmat" = '100'::float4 AND "subbonprijsbfaankoopmet" = '0'::float4 AND "subbonprijsbfaankoopond" = '0'::float4 AND "subbonprijsbfaankoopdir" = '0'::float4 AND "subbonprijsbfaankoopbouw" = '0'::float4 AND "subbonprijsbfaankoopb1" = '0'::float4 AND "subbonprijsbfaankoopb2" = '0'::float4 AND "subbonprijsbfaankoopb3" = '0'::float4 AND "margeuur" = '10'::float4 AND "margemat" = '10'::float4 AND "margemet" = '0'::float4 AND "margeond" = '0'::float4 AND "margedir" = '0'::float4 AND "margebouw" = '0'::float4 AND "margeb1" = '0'::float4 AND "margeb2" = '0'::float4 AND "margeb3" = '0'::float4 AND "aankoopprijs" = '1' AND "verkoopprijs" = '0' AND "subbontotaalbfaankoop" = '1000'::float4 AND "korting" = '10'::float4 AND "datum1" IS NULL AND "aantal1" = '0'::float4 AND "datum2" IS NULL AND "aantal2" = '0'::float4 AND "checkaantal2" = '0' AND "datum3" IS NULL AND "aantal3" = '0'::float4 AND "checkaantal3" = '0' AND "datum4" IS NULL AND "aantal4" = '0'::float4 AND "totaalgeleverd" = '0'::float4 AND "nogteleveren" = '0'::float4 AND "aantalgefaktureerd" = '0'::float4 AND "referentiegefaktureerd" IS NULL AND "onderaannemer" IS NULL AND "onderaannemerh" IS NULL AND "vinkje" = '0' AND "toegewezen" = ' ' AND "afdrukr" = '0' AND "barcode" IS NULL AND "begindatum" IS NULL AND "einddatum" IS NULL AND "kleurprijs1" = '0'::float4 AND "kleurprijs2" = '0'::float4 AND "kleurprijs3" = '0'::float4 AND "kleurprijs4" = '0'::float4 AND "kleurprijs5" = '0'::float4 AND "kleurprijs6" = '0'::float4 AND "kleurprijs7" = '0'::float4 AND "kleurprijs8" = '0'::float4 AND "kleurprijs9" = '0'::float4 AND "kleurprijs10" = '0'::float4 AND "stockprodukt" = '0' AND "lengte" = '1'::float4 AND "breedte" = '1'::float4 AND "hoogte" = '1'::float4 AND "lettersb" IS NULL AND "lettersf" IS NULL AND "zuiverekost" = '1000'::float4 AND "afdrukuurtabel" = '1' AND "werfref" = '2410' AND "productieref" = ' ' AND "uwref" = ' ' AND "aanbesteding01" = '0'::float4 AND "aanbesteding02" = '0'::float4 AND "aanbesteding03" = '0'::float4 AND "aanbesteding04" = '0'::float4 AND "aanbesteding05" = '0'::float4 AND "aanbestedingmarge" = '0'::float4 AND "aanbestedingvp" = '0'::float4 AND "aanbestedingep" = '0'::float4 AND "aanbestedingvpa" = '0'::float4 AND "aanbestedingepa" = '0'::float4 AND "aanbestedingmargea" = '0'::float4 AND "onderaannemeraanbesteding" IS NULL AND "bevatdetailstaat" = '0' AND "groepselectie" = '0' AND "doorrekenenbk" = '1' AND "doorrekenenu" = '1' AND "zuiverkostminuren" = '1000'::float4 AND "merk" IS NULL AND "leverancier" = 0 AND "transferred" = '0' AND "nietmeerekenen" = '0

I did change the field 'subbonbeschrijving' from Test1000 into Test1000a, so the sql query access sends seems to be ok.
What am I doing wrong. I looked in the manuel but I can't seem to find what goes wrong.

Thanks

Dirk Cleenwerck

pgsql-novice by date:

Previous
From: Miguel Higuera
Date:
Subject: scripts
Next
From: Randolf Richardson
Date:
Subject: Re: postgresql or mysql or oracle?