Message-ID: <50F6D6FF.8000504@gmail.com>
Date: Wed, 16 Jan 2013 09:36:15 -0700
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130106 Thunderbird/17.0.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: returning the number of rows output by a copy command from
	a function
References: <CD1C3FE5.6D0E%jsharrett@tidemark.net>
In-Reply-To: <CD1C3FE5.6D0E%jsharrett@tidemark.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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