Exit PL / pgSQL Variable

Question

I have a trigger for PostgreSQL 9 as follows:

CREATE OR REPLACE FUNCTION clients_update_billingdata_trigger() RETURNS trigger AS $BODY$ DECLARE columnsUpdate TEXT; BEGIN columnsUpdate := ''; IF (NEW.rsocial IS DISTINCT FROM OLD.rsocial) THEN columnsUpdate := columnsUpdate || 'RSocial before: ' || OLD.rsocial || '. RSocial after: ' || NEW.rsocial || E'\n'; END IF; IF (NEW.legalidentifier IS DISTINCT FROM OLD.legalidentifier) THEN columnsUpdate := columnsUpdate || 'ILegal before: ' || OLD.legalidentifier || '. ILegal after: ' || NEW.legalidentifier || E'\n'; END IF; [...] IF (columnsUpdate != '') THEN SELECT dblink_exec ('dbname=xxx user=xxx password=xxxxx', 'INSERT INTO BillingDataUpdate (client_id, columnsupdate) VALUES (''' || NEW.idclient || ''', ''' || columnsUpdate || ''');'); END IF; RETURN NEW; END; $BODY$ LANGUAGE plpgsql; 

The values ​​of NEW.rsocial can be, for example, a Tommy service. If I turn off the trigger, the record will be saved correctly (in the other table, in Clients), because I avoid the line in PHP using the pg_escape_string function. The question is how to start NEW.rsocial to trigger a trigger?

Thanks in advance.

Answer 1

The functions quote_literal and quote_nullable can be useful. But be careful that these are PostgreSQL functions, so make sure the other side of DBLINK understands the result.

You can also take a look at this part of the docs:

http://www.postgresql.org/docs/9.1/interactive/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE

EDIT

quote_xyz should not be applied to rsocial customs, but to dblink_exec .

  SELECT dblink_exec ('dbname=xxx user=xxx password=xxxxx', 'INSERT INTO BillingDataUpdate (client_id, columnsupdate) ' || 'VALUES (' || quote_nullable(NEW.idclient) || ', ' || quote_nullable(columnsUpdate) || ');'); 

And notice the changed number ' in string concatenation.