The # rows in the table <> # rows in the file because the table is grouped and aggregated so simple table row count wouldn't be accurate. The table can run in the 75M - 100M range so I was trying to avoid running all the aggregations once to output the file and then run the same code again just to get a count.
On 1/16/13 11:36 AM, "Rob Sargent" <robjsarg...@gmail.com> wrote: >On 01/16/2013 09:30 AM, James Sharrett wrote: >> I have a function that generates a table of records and then a SQL >> statement that does a COPY into a text file. I want to return the >> number of records output into the text file from my function. The >> number of rows in the table is not necessarily the number of rows in the >> file due to summarization of data in the table on the way out. Here is >> a very shortened version of what I'm doing: >> >> >> CREATE OR REPLACE FUNCTION export_data(list of parameters) >> RETURNS integer AS >> $BODY$ >> >> declare >> My variables >> >> Begin >> >> { A lot of SQL to build and populate the table of records to export} >> >> >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with >> CSV HEADER;'; >> Execute strSQL; >> >> Return 0; >> >> end >> $BODY$ >> LANGUAGE plpgsql VOLATILE >> >> strSQL gets dynamically generated so it's not a static statement. >> >> This all works exactly as I want. But when I try to get the row count >> back out I cannot get it. I've tried the following: >> >> 1. >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with >> CSV HEADER;'; >> Execute strSQL into export_count; >> >> Return export_count; >> >> This give me an error saying that I've tried to use the INTO statement >> with a command that doesn't return data. >> >> >> 2. >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with >> CSV HEADER;'; >> Execute strSQL; >> >> Get diagnostics export_count = row_count; >> >> This always returns zero. >> >> 3. >> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with >> CSV HEADER;'; >> Execute strSQL; >> >> Return row_count; >> >> This returns a null. >> >> Any way to do this? >> >> >> Thanks in advance, >> James >> >declare export_count int; > >select count(*) from export_table into export_count(); >raise notice 'Exported % rows', export_count; > > > >-- >Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql