Postgresql – Use plpgsql to count records selected for output AND output those records

countpostgresqlselect

My query SELECTs 1 row from table1, which has a number of fields plus one very long text field. It JOINs with a second table2, that may have many matching corresponding rows.
I would like to be able to spread the long text field, from table1, out over multiple lines (the number determined by the matching rows in table2).

enter image description here

In the table above there is one row from Table1 where the key is table1_key = 'DU' and year = '2017'. Then there are four rows from Table2 with matching keys that include the programme_code and course_code. The field long_text_field, which I want to spread over multiple lines, comes from Table1.

Is it possible to extract a total row count for records that have been selected for output and still output the rows? Meaning that I want to see a count of 4 from the above and be able to work with this number in the displayed output. So that the result looks something like this:

enter image description here

Best Answer

I don't get your example, but if that's just an illustration of the question in the heading, the answer is a window function:

SELECT /* all your columns and expressions */,
       count(*) OVER () AS total_count
FROM /* the rest of your query */

This will append a column to each result row with the total number of result rows.

The window function is called after GROUP BY, but before ORDER BY and LIMIT.

Related Question