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

2013/1/16 James Sharrett <jsharrett(at)tidemark(dot)net>:
> 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?
>

not yet

it is fixed in 9.3

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7ae1815961c635fd1a6fe72acb89fdef741a45a8

Regards

Pavel Stehule

>
> Thanks in advance,
> James
>