From: James Sharrett <jsharrett@tidemark.net>
To: pgsql-sql@postgresql.org
Subject: Re: returning the number of rows output by a copy command from a function
Date: Wed, 16 Jan 2013 12:13:41 -0500
Message-ID: <CD1C495B.6D17%jsharrett@tidemark.net> (raw)
In-Reply-To: <50F6D6FF.8000504@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jsharrett@tidemark.net
Subject: Re: returning the number of rows output by a copy command from a function
In-Reply-To: <CD1C495B.6D17%jsharrett@tidemark.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox