CODEWITHSUNDEEP
postgresql
Dusty
Dusty

Reputation: 2333

Returning inserted rows in PostgreSQL

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)

Upvotes: 37

Views: 36650

Answers (3)

dman
dman

Reputation: 11064

Or without select:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
   RETURNING did;

documentation

Upvotes: 31

Joshua D. Drake
Joshua D. Drake

Reputation: 1012

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

Upvotes: 4

Matthew Wood
Matthew Wood

Reputation: 16417

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

Upvotes: 64

Related Questions