User-Agent: Microsoft-MacOutlook/14.2.5.121010
Date: Wed, 16 Jan 2013 12:13:41 -0500
Subject: Re: returning the number of rows output by a copy command from
	a function
From: James Sharrett <jsharrett@tidemark.net>
To: <pgsql-sql@postgresql.org>
Message-ID: <CD1C495B.6D17%jsharrett@tidemark.net>
Thread-Topic: [SQL] returning the number of rows output by a copy command from
	a function
In-Reply-To: <50F6D6FF.8000504@gmail.com>
Mime-version: 1.0
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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