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:16:08 -0500
Message-ID: <CD1C4A00.6D1D%jsharrett@tidemark.net> (raw)
In-Reply-To: <50F6D883.50901@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
The problem I have is that I get nothing back when the COPY is run inside
the function other than what I explicitly return from the function so I
don't have anything to parse. It's odd that the record count in the
function is treated differently than from sql query in GET DIAGNOSTIC
since the format and information in the string (when run outside of the
function) are exactly the same.
On 1/16/13 11:42 AM, "Adrian Klaver" <adrian.klaver@gmail.com> wrote:
>On 01/16/2013 08: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?
>
>If it helps:
>http://www.postgresql.org/docs/9.2/interactive/sql-copy.html
>"
>On successful completion, a COPY command returns a command tag of the form
>
>COPY count
>The count is the number of rows copied.
>"
>
>So it looks like you will need to parse the string for the count.
>
>
>>
>>
>> Thanks in advance,
>> James
>>
>
>
>--
>Adrian Klaver
>adrian.klaver@gmail.com
--
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: <CD1C4A00.6D1D%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