Postgresql – Insert data in multiple tables

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;

The hint of the error message is right on target:

You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

Bold emphasis mine. Per documentation:

RETURNING queries on the view will be rejected if there is no RETURNING clause in any available rule.

Don't confuse this with the RETURNS clause of your function. Not the same thing.

Postgres looks for an actual RETURNING clause of an INSERT, UPDATE or DELETE statement. It has to be a plain SQL statement. There can only be one RETURNING clause, even if there are multiple statements in the rule:

furthermore there can be at most one RETURNING clause among all the rules for the same event.

It will only be executed if the outer call has a RETURNING clause, too.

Since you are inserting into two tables, it gets slightly complicated. Either one of the two INSERT statements in the rule only has the columns of one target table at it's disposal. You would need to select from the other table (or from the view itself) in the RETURNING clause to provide a complete row.

Also, the returned value from the serial column cannot be stored in a variable, since there are no variables in plain SQL. I solved it with lastval() instead.

And NEW is not visible in a CTE, so you can't chain data-modifying CTE either.

But it can be done anyway:

CREATE OR REPLACE RULE custom_insert_gi_joe AS
ON INSERT TO gi_joe DO INSTEAD (

 INSERT INTO gi(weapon_type_id, fire_power, created_at, updated_at)
 VALUES (NEW.weapon_type, NEW.weapon_fire_power, NEW.created_at, NEW.updated_at);

 INSERT INTO joe(first_name, last_name, city, country
               , gi_id, external_id, created_at, updated_at)
 VALUES(NEW.first_name, NEW.last_name, NEW.city, NEW.country, lastval()
      ,(SELECT joe_id FROM external WHERE id = NEW.external_id LIMIT 1)
      , NEW.created_at, NEW.updated_at)
 RETURNING
   joe.gi_id,
   NULL::int,
   NULL::int,
   joe.first_name,
   joe.last_name,
   joe.city,
   joe.country,
   joe.external_id,
   NULL::timestamp,
   NULL::timestamp
);

SQL Fiddle.

I didn't bother to provide the complete row and just filled in NULL values for missing columns from gi, cast to respective types. This could be solved, too, but since you are only interested in the id column, it would be cost for no gain.

Now, this works as expected:

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                  ,country,created_at,updated_at) 
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW())
RETURNING id;