Menu
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)
650
PostgreSQL used the OID internally as a primary key for its system tables. Typically, the INSERT statement returns OID with value 0. The count is the number of rows that the INSERT statement inserted successfully.
To return a table from the function, you use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (, ). In the function, we return a query that is a result of a SELECT statement.
The COUNT(*) function returns the number of rows returned by a SELECT statement, including NULL and duplicates. When you apply the COUNT(*) function to the entire table, PostgreSQL has to scan the whole table sequentially.
In PostgreSQL, the dollar-quoted string constants ($$) is used in user-defined functions and stored procedures. In PostgreSQL, you use single quotes for a string constant like this: select 'String constant'; When a string constant contains a single quote ('), you need to escape it by doubling up the single quote.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
