I'm generating a query on the fly to return a set of data, however I only want to display 30 rows at a time to the user. For this reason, I use the LIMIT 30 OFFSET x clause on the select statement. However, I also want to know the total rows that match this query had there been no limit, that way I can display to the user the total count and the number of pages, and have Next/Prev buttons in my UI. I can think of the following ways to do this: 1) When the page loads, execute two totally separate queries. One that does the COUNT, and then another query immediately after to get the desired page of data. I don't like this as much because the two queries will execute in separate transactions and it'd be nice if I could just perform a single SQL query and get all this information at once. However, I will use this if there's no other way. 2) Execute two select statements in a single SQL query. The first table will contain a single row and column with just the count, then the second table will contain the results for the page. This should work in practice, but I don't believe Npgsql supports the idea of a single query returning multiple tables. Or this might be a Postgres limitation, I'm not sure. MSSQL supports it, however. 3) Do something weird with query parameters or return values. I've messed around with this, but I don't think parameters, variables, or return values are supported outside a stored function call. Since I'm generating my SQL statement on the fly, I'm not calling a function. I could write a function that takes parameters for all the values, however I'm JOIN'ing different tables depending on what the user is searching for. For example, I don't join in the users table unless they're filtering some sort of property of the user. Anyone ran into this situation before? What's the best approach here. Thanks! Mike -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
