Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Unanswered: Question about updatable view using dblink

    I'm connecting to a second database on the same host through a view:
    The view works as i see in it the data from the table.
    Next i created a rule to update the source table:
    I tailored the code for the rule from the documentation and some examples on the web. I'm not certain it is correct. When i try to update data in the view i get the following error:
    Query failed: ERROR: missing FROM-clause entry for table "new" CONTEXT: Error occurred on dblink connection named "unnamed"

    Tried googling it but to no effect so far. It would be great if someone could just point me in the right direction.

    Regards,
    Pustka

    postgres 9.1
    debian 2.6.26-1-amd64
    (make sure u installed the postgres-contrib)

    Code:
    CREATE DATABASE db1;
    CREATE TABLE users
    (
      uid serial NOT NULL,
      name character varying(60) NOT NULL DEFAULT ''::character varying,
      pass character varying(32) NOT NULL DEFAULT ''::character varying,
      CONSTRAINT users_pkey PRIMARY KEY (uid )
    )WITH(   OIDS=FALSE );
    ALTER TABLE users OWNER TO test;
    
    CREATE DATABASE db2;
    \connect db2;
    CREATE extension dblink;
    
    CREATE VIEW users AS SELECT usersxx.*
    FROM dblink('hostaddr=127.0.0.1 port=5432 dbname=db1 user=test password=test'::text,
    'SELECT uid, name, pass FROM users'::text)
        AS usersxx(uid integer,   name character varying(60),   pass character varying(32));
    
    CREATE RULE users_update AS ON UPDATE TO users DO INSTEAD SELECT 
        dblink_exec('hostaddr=127.0.0.1 port=5432 dbname=db1 user=test password=test'::text,
                    'UPDATE users SET name = NEW.name, pass = NEW.pass WHERE uid = NEW.uid '::text) AS dblink_exec;
    Last edited by pustka; 11-04-11 at 08:37.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •