The other option would be to create temp table... Execute dynamic sql to fil temp table
Copy from temp table - copy will return number of rowsx (not inside execute...) Drop temp Kind regards, Misa On Wednesday, January 16, 2013, Misa Simic wrote: > I meant the count from the same query as for copy command what actually go > to file... Not count rows from table... > > But i agree could be slow... > > Cheers, > > Misa > > > > On Wednesday, January 16, 2013, James Sharrett wrote: > >> 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 >> >