Skip site navigation (1) Skip section navigation (2)

Bug Attached Table Ms Access97 with Boolean

From: Ярослав Якубовский <quick_yak(at)21centure(dot)ru>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Bug Attached Table Ms Access97 with Boolean
Date: 2008-02-21 13:26:16
Message-ID: 002801c8748d$567184b0$32b1acac@NOVEMBER (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello!
Sorry for my English.

Test on server PG 8.3.0.1 and ODBC driver 8.3.100. MS Acces 97 + service pack 2.

I has trouble: with BOOLEAN when INSERT INTO VIEW - after INSERT state affected records is locked and don't edit by manually. It's edit only by SQL. It's appear if there is a Boolean field in VIEW and SQL don't include it's.
By Examle:

CREATE TABLE "public"."firm" (
  "id" INTEGER NOT NULL, 
  "is_active" BOOLEAN DEFAULT false, 
  "firm" VARCHAR(150), 
  CONSTRAINT "firms_idx_pkey" PRIMARY KEY("id")
) WITH OIDS;

------------------------

CREATE VIEW "public"."vw_firm_bool" (
    "id",
    "is_active",
    "firm")
AS
SELECT f."id", f."is_active", f."firm
FROM firm f;

CREATE RULE "vw_firm_bool_rl" AS ON INSERT TO "public"."vw_firm_bool" 
DO INSTEAD (INSERT INTO firm ("id", "is_active", "firm") VALUES (new."id", new."is_active", new."firm););

/* working version
CREATE RULE "vw_firm_bool_rl" AS ON INSERT TO "public"."vw_firm_bool" 
DO INSTEAD (INSERT INTO firm ("id", "is_active", "firm") VALUES (new."id", COALESCE(new."is_active", false), new."firm););
*/

CREATE RULE "vw_firm_bool_rl1" AS ON UPDATE TO "public"."vw_firm_bool" 
DO INSTEAD (UPDATE firm SET "is_active" = new."is_active", "firm" = new."firm" WHERE firm."id" = new."id";);

CREATE RULE "vw_firm_bool_rl2" AS ON DELETE TO "public"."vw_firm_bool" 
DO NOTHING;

-----------------

If I attached view "vw_firm_bool_rl" in MS Access 97 as linked table "vw_firm_bool_rl" and execute SQL query:
"INSERT INTO vw_firm_bool_rl (id, firm) VALUES (15, 'new firms')"
(explicit pair: "is_active - false" is apsent) then is_active in table = NULL  (not false, not true) and record is locked by opening in table mode in MS Access "vw_firm_bool_rl" by manually

WorkAround:
1. Explicit list all Boolean fields in SQL Query "Insert" on side attached table
2. Pg - Rule on view INSERT - COALESCE(new . "<boolean>", false)
3. CREATE TRIGGER "table1_tr" BEFORE INSERT 
ON "public"."table1" FOR EACH ROW 
EXECUTE PROCEDURE "public"."table1_tr_date"();

CREATE OR REPLACE FUNCTION "public"."table1_tr_date" () RETURNS trigger AS
$body$
BEGIN
  /* New function body */
       -- Remember when changed
        NEW.tms := current_timestamp;
        NEW."is_active" := COALESCE(NEW."is_active",  false);  
        -- wanted DEFAULT  VALUE this field, i.e. COALESCE(NEW."is_active", <DEFAULT>, false)
        RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


Yaroslav Yakubovskij
E-mail: quick_yak(at)21centure(dot)ru
ICQ: 327185501

pgsql-bugs by date

Next:From: Laurent BarbierDate: 2008-02-21 16:34:41
Subject: Initdb error without much more details PostgreSQL 7.4.19
Previous:From: AnnaDate: 2008-02-21 12:28:38
Subject: BUG #3976: Inteface direction issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group