[ic] Interchange and SQL-Ledger / dblink / and psql triggers (LONG)

[Andrew Baerg]

On Tue, 25 Jan 2005 14:07:25 -0500, David Ratte wrote 
> Hello all, 
>  
> Last week I posed a question regarding getting data into SQL-Ledger  
> from a foreign application (i.e. interchange) and if anyone had any  
> successes with this configuration. 
>  
> The use of dblink and triggers. This was very interesting because it  
> means total independance of both applications as to keeping the data  
> in sync -- and either application changing would not effect this  
> stuff from working unless the db  structure changed. 
>  
> Pardon my length, but some might find what I'm doing useful, and  
> others to tell me why it won't work! 
>  
> Now this code here does work, but only if the sql-ledger customer  
> table is in the same database as interchanges userdb. 
>  
> I guess the ultimate question is, Can dblink be used inside an  
> plpgsql procedure, or is this just plain illegal?? 
 
As far as I know there is nothing illegal about this. Without taking the 
time to troubleshoot your code, I will give you a snippet of some plpgsql 
code using dblink which works for me. This code updates the userdb->company 
field with the customer->name field on insert/update on the sql-ledger 
customer table. The userdb and customer tables are in seperate databases, 
but on the same host, but it should make no difference if the databases were 
on different hosts. I am using postgresql 7.4.6 
 
===BEGIN CODE=== 
CREATE OR REPLACE FUNCTION customer_update() 
 RETURNS trigger AS 
'DECLARE 
    userdb_query text; 
    name text; 
    customernumber text; 
BEGIN 
    name := NEW.name; 
    customernumber := NEW.customernumber; 
    PERFORM dblink_connect(''dbname'',''dbname=dbname user=username''); 
    userdb_query :=  ''SELECT dblink_exec (''''dbname'''', ''''UPDATE userdb 
SET company = '''''' 
        || quote_literal(NEW.name) 
        || '''''' WHERE username = '''''' 
        || quote_literal(NEW.customernumber) 
        || '''''' '''')''; 
    EXECUTE userdb_query; 
    PERFORM dblink_disconnect(''edge''); 
RETURN NEW; 
END; 
' 
 LANGUAGE 'plpgsql' VOLATILE; 
 
drop trigger customer_update on customer; 
 
CREATE TRIGGER customer_update 
 AFTER UPDATE 
 ON customer 
 FOR EACH ROW 
 EXECUTE PROCEDURE customer_update(); 
 
===END CODE=== 
 
The quoting was enough to drive me crazy, but it does work very nicely. I am 
currently customizing interchange+sql-ledger for a company and will be 
making use of dblink for sql-ledger -> interchange synchronization.  
 
Andrew Baerg 
 
>  
> If I throw dblink into the mix, everything breaks: 
>  
> (working query) 
> select * from dblink_exec('testdb','insert into customer (id,name)values 
>  (DEFAULT,''i24NULL'')'); dblink_exec 
>    ----------------- 
>     INSERT 201975 1 
>     (1 row) 
>  
> this however, breaks down at hostname: 
> ===BAD CODE SNIP=== 
> CREATE OR REPLACE FUNCTION user_update() 
>  RETURNS trigger AS 
>  'BEGIN 
>    select * from dblink('hostaddr=192.168.1.1 dbname=test 
>  user=test','select name from customer') as t1(name varchar) ; 
>    if exists( select 1 from dblink ('hostaddr=192.168.1.1 dbname=test 
>  user=test', 'select * from customer WHERE customer.name = 
>  (new.lname||'', ''||NEW.fname)') ) then 
> ===END BAD CODE=== 
>  
> Here's what I came up with (and it works, but so far without dblink): 
>  
> ===BEGIN CODE update_user.sql=== 
> CREATE OR REPLACE FUNCTION user_update() 
>  RETURNS trigger AS ' 
>  BEGIN 
> --Let's see if the customer already exists in sql-ledger's customer 
database 
> --If the customer already exists, just update the existing record. 
>   if exists( select 1 from customer WHERE customer.name =  
> (new.lname||'', ''||NEW.fname) ) then UPDATE customer SET    --sql- 
> ledger has only one name field, interchange has lastname and  
> firstname -- Ucase(lcase(name)) normalizes 'jOe sMiTh' into 'Smith, 
>  Joe' to prevent dupes in sql-ledger     name = (initcap(NEW.lname) 
>  ||'', '' || initcap(NEW.fname)),    address1 = NEW.address1,         
>    address2=NEW.address2,           city=NEW.city,            
> state=NEW.state,           zipcode=NEW.zip,           
>  email=NEW.email,           phone=NEW.phone_day,            
> contact=initcap(NEW.fname)||'' ''||initcap(NEW.lname)        WHERE  
> customer.name = (new.lname||'', ''||NEW.fname);   else --Since we  
> decided the customer did not exist, lets add a new customer record  
> INSERT INTO customer ( name, address1, address2, city, state, 
>  zipcode, email, phone, contact ) VALUES (initcap(NEW.lname)||'', ''  
> ||initcap(NEW.fname),           NEW.address1,           NEW.address2, 
>            NEW.city,           NEW.state,           NEW.zip,          
>   NEW.email,           NEW.phone_day,           initcap(NEW.fname) 
> ||'' ''||initcap(NEW.lname)); 
>  
>    end if; 
>  
>    RETURN NEW; 
>    END; 
>    ' 
>    LANGUAGE 'plpgsql' VOLATILE; 
>  
> --If the trigger already exists, get rid of it so we can re-create it 
> DROP TRIGGER trans_update on transactions; 
>  
> CREATE TRIGGER trans_update 
>     BEFORE UPDATE 
>     ON transactions 
>     FOR EACH ROW 
> ====END CODE==== 
>  
> Thank you for your response, and please trim responses accordingly  
> to keep mail size down. 
>  
> -Dave Ratte 
>  
> ------------------------------------------------------- 
> _______________________________________________ 
> interchange-users mailing list 
> interchange-users at icdevgroup.org 
> http://www.icdevgroup.org/mailman/listinfo/interchange-users 
 
 
-- 
SOLE Custom Footbeds (http://www.yoursole.com)