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 >