Sql-server – How to insert in table from remote stored procedure without creating a distributed transaction

You could use a data-modifying CTE (introduced with PostgreSQL 9.1) for that to perform well. Consider the following demo:

CREATE OR REPLACE FUNCTION f_fetch3()
  RETURNS text AS
$func$

SELECT public.dblink_connect(
      'hostaddr=?.?.?.? port=5432 dbname=db user=postgres password=???');

-- Truncate tables first?
-- TRUNCATE tbl1, tbl2, tbl3;

WITH i AS (
    SELECT *
    FROM public.dblink(
        'SELECT id
               ,col1
               ,col2
               ,col3
        FROM    remote_tbl'
         ) AS d (id int, col1 text, col2 text, col3 text)
    )
    , x AS (
    INSERT INTO tbl1
    SELECT id, col1
    FROM   i
    )
    , y AS (
    INSERT INTO tbl2
    SELECT id, col2
    FROM   i
    )
INSERT INTO tbl3
SELECT id, col3
FROM   i;

/* -- Analyze tables?
ANALYZE tbl1;
ANALYZE tbl2;
ANALYZE tbl3;
*/

SELECT public.dblink_disconnect();

$func$ LANGUAGE sql VOLATILE SECURITY DEFINER
SET search_path=myschema, pg_temp;

ALTER FUNCTION f_fetch3() OWNER TO postgres;
REVOKE ALL ON FUNCTION f_fetch3() FROM public;

Call:

SELECT f_fetch3();

Tested with PostgreSQL 9.1.4.

If you put your password here, I would create that function in a separate schema and remove all access from schema and function from the general public.

You obviously have dblink installed, but for the general public: the extension has to be installed once per database:

CREATE EXTENSION dblink;

It might be worth looking at XACT_STATE instead of @@TRANCOUNT.

From BOL:

Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction.

So there may be some cases where, even though there was an error, the transaction should still be committed to the database.

IF (XACT_STATE()) = -1
BEGIN
    PRINT 'The transaction is in an uncommittable state.' +
          ' Rolling back transaction.'
    ROLLBACK TRANSACTION;
END;

-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
    PRINT 'The transaction is committable.' + 
          ' Committing transaction.'
    COMMIT TRANSACTION;   
END;

Of course, it's entirely up to you whether you want to commit any transaction where an error was raised, even if it is committable... Just because you can, doesn't mean it's always a good idea!