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

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" <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
>>
>