Returning inserted rows in PostgreSQL

Question

I'm currently working on a report generation servlet that agglomerates information from several tables and generates a report. In addition to returning the resulting rows, I'm also storing them into a reports table so they won't need to be regenerated later, and will persist if the tables they're drawn from are wiped. To do the latter I have a statement of the form (NB: x is externally generated and actually a constant in this statement):

INSERT INTO reports
   (report_id, col_a, col_b, col_c)
SELECT x as report_id, foo.a, bar.b, bar.c
FROM foo, bar

This works fine, but then I need a second query to actually return the resulting rows back, e.g.

SELECT col_a, col_b, col_c
FROM reports
WHERE report_id = x

This works fine and since it only involves the single table, shouldn't be expensive, but seems like I should be able to directly return the results of the insertion avoiding the second query. Is there some syntax for doing this I've not been able to find? (I should note, I'm fairly new at DB work, so if the right answer is to just run the second query, as it's only slightly slower, so be it)

Answer 1

In PostgreSQL with version >= 8.2, you can use this construct:

INSERT INTO reports (report_id, col_a, col_b, col_c)
SELECT x as report_id, foo.a, bar.b, bar.c
FROM foo, bar
RETURNING col_a, col_b, col_c

Answer 2

You could also use an SRF although that may be overkill. It depends on what you are trying to do. For example, if you are only returning the information to perform a piece of logic that will go directly back to the database to perform more queries, it may make sense to use an SRF.

http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions