[sql] postgresql: INSERT INTO ... (SELECT * ...)

I'm not sure if its standard SQL:

 INSERT INTO tblA 
 (SELECT id, time 
    FROM tblB 
   WHERE time > 1000)  

What I'm looking for is: what if tblA and tblB are in different DB Servers.

Does PostgreSql gives any utility or has any functionality that will help to use INSERT query with PGresult struct

I mean SELECT id, time FROM tblB ... will return a PGresult* on using PQexec. Is it possible to use this struct in another PQexec to execute an INSERT command.

EDIT:
If not possible then I would go for extracting the values from PQresult* and create a multiple INSERT statement syntax like:

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'); 

Is it possible to create a prepared statement out of this!! :(

You can use dblink to create a view that is resolved in another database. This database may be on another server.

If you want insert into specify column:

INSERT INTO table (time)
(SELECT time FROM 
    dblink('dbname=dbtest', 'SELECT time FROM tblB') AS t(time integer) 
    WHERE time > 1000
);

If you are looking for PERFORMANCE, give where condition inside the db link query. Otherwise it fetch all data from the foreign table and apply the where condition.

INSERT INTO tblA (id,time) 
SELECT id, time FROM  dblink('dbname=dbname port=5432 host=10.10.90.190 user=postgresuser password=pass123', 
'select id, time from tblB  where time>'''||1000||'''')
AS t1(id integer, time integer)  

Here's an alternate solution, without using dblink.

Suppose B represents the source database and A represents the target database: Then,

1. Copy table from source DB to target DB:

   pg_dump -t <source_table> <source_db> | psql <target_db>
   

2. Open psql prompt, connect to target_db, and use a simple insert:

   psql
   # \c <target_db>;
   # INSERT INTO <target_table>(id, x, y) SELECT id, x, y FROM <source_table>;
   

3. At the end, delete the copy of source_table that you created in target_table.

   # DROP TABLE <source_table>;
   

This notation (first seen here) looks useful too:

insert into postagem (
  resumopostagem,
  textopostagem,
  dtliberacaopostagem,
  idmediaimgpostagem,
  idcatolico,
  idminisermao,
  idtipopostagem
) select
  resumominisermao,
  textominisermao,
  diaminisermao,
  idmediaimgminisermao,
  idcatolico ,
  idminisermao,
  1
from
  minisermao    

insert into TABLENAMEA (A,B,C,D) 
select A::integer,B,C,D from TABLENAMEB