I recently ran into the same problem and it gave me quite a headache. I tried with a plpgsql function, but ROW_COUNT is not set by COPY. I consider this a bit of a bug or at least a missing feature.
You could just run two queries. First count() and then COPY. With simple queries that's probably the way to go. But with huge / complex queries this is a pain and can double the execution time.
I came up with a solution that uses a temporary table and counts the rows before executing COPY.
I adapted what I have for you so you can COPY TO STDOUT and pipe to bzip2, which is not possible from within a plpgsql function:
1) Create a function that takes an SQL string and creates a temporary table with it:
CREATE OR REPLACE FUNCTION f_copy_prep(_query text)
RETURNS void AS
$BODY$
DECLARE
ct integer;
BEGIN
-- If you deal with huge results, set more RAM for temporary tables locally:
-- SET temp_buffers = 512MB'; -- example value
EXECUTE 'CREATE TEMP TABLE cp_tmp ON COMMIT DROP AS (' || _query || ')';
GET DIAGNOSTICS ct = ROW_COUNT;
RAISE LOG 'My text here. rows: %', ct;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION f_copy_prep(text) SET search_path=public,pg_temp;
REVOKE ALL ON FUNCTION f_copy_prep(text) FROM public;
GRANT EXECUTE ON FUNCTION f_copy_prep(text) TO ???;
Function executes dynamic SQL, so you can use it for any query.
This is inherently unsafe, so run it with the minimum rights necessary, revoke all rights from public and grant EXECUTE exclusively to a trusted user. Follow the instructions in the manual!
Create temporary table with ON COMMIT DROP, so it gets dropped automatically at the end of the transaction.
Get the row count with GET DIAGNOSTICS - ROW_COUNT is set by the SELECT statement in EXECUTE. Write it to the log - your requirement. No need for a separate count(*).
2) Call from shell-script to pipe output through bzip2
psql "connection parameters" \
-c "SELECT f_copy_prep('SELECT * FROM tbl WHERE insertion_date > ''date'''); \
COPY cp_tmp TO STDOUT WITH CSV HEADER;" \
| bzip2 -c > backup.csv.bz2
Put two SQL commands into your -c argument or put complex queries in a file and use the -f parameter. All is executed in one transaction. Only the output of the last command is returned - fits our need. Careful with the syntax - multiple layers of interpretation (first shell, then Postgres).
First command is the above function with your query-string as parameter. Second is the COPY TO STDOUT.
I tested this with PostgreSQL 9.1 on Linux and it worked for me: data in the file, message with row-count in the log.
