returning the number of rows output by a copy command from a function

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" <robjsargent(at)gmail(dot)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(at)postgresql(dot)org)
> >To make changes to your subscription:
> >http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>