Performance difference "insert into select" vs "select and loop for insert"

Is there any performance benefits in

INSERT INTO table1( column1) select * from tab开发者_JAVA技巧le2;

against

BEGIN
FOR t2Row IN SELECT * FROM table2 LOOP
   EXECUTE 'INSERT INTO ...'
END LOOP;
END;

Mostly interesting in postgresql db engine. I believe that first query consume more memory since it buffer whole result, but is first query faster ?

There is a SO answer that touches on this subject here: RBAR vs. Set based programming for SQL

I'd say that the first approach is much faster than the second as it is set-based (as mentioned by marc_s).

Also, the second method involves switching to SQL and then back again for each iteration of the loop. I am not a Postgres user but in Oracle this would cause a performance overhead in the context switching alone, depending upon the number of records involved it could be very significant.

The first method is also the simplest and in most environments the simplest method is the easiest to support.

To answer your question though, the first method is faster than the second.