Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvVtG-0004Tl-W3 for pgsql-sql@arkaria.postgresql.org; Wed, 16 Jan 2013 16:31:03 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TvVtG-0005GO-Gz for pgsql-sql@arkaria.postgresql.org; Wed, 16 Jan 2013 16:31:02 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvVtF-0005GH-H0 for pgsql-sql@postgresql.org; Wed, 16 Jan 2013 16:31:01 +0000 Received: from mail-ie0-f173.google.com ([209.85.223.173]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TvVt7-000285-L5 for pgsql-sql@postgresql.org; Wed, 16 Jan 2013 16:31:00 +0000 Received: by mail-ie0-f173.google.com with SMTP id e13so2854472iej.4 for ; Wed, 16 Jan 2013 08:30:52 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=x-received:user-agent:date:subject:from:to:message-id:thread-topic :mime-version:content-type:x-gm-message-state; bh=ZVeACF3W/zuUchkYE0YsIHdlUoqUdekGuG3hvH0u8d4=; b=c33PnvNLamkMj4jySh7isSZHmnJmWyDYZ1H6H4Twl0eDWdkvl3/UHD50dO57rLanfe 5NSz0h7pmvoldDiWdwU8QaZk8BWjIEwJAzmrZVpTg7yosc1nUSN3P4DjUKGPjYrGCNnd 1YsCt1Y1NhXot8N89C9ojNZ2NBEvfyOIqmuaRa+khItGNMfb6IaA0+JNr3ggQh7Y7kHf yeYsmAw3NbiSxZU+dj2ZPshWDYmuo737qskgWjknWGlJf4K5XoQ9L1wHp3G7eaPaROBu +4CSuM0DR5RwU0Y0ydg9owt8+e2jbSf9Ju7jQUDuFPr+1DQsTo/G7/RVGf99d66mdSQ5 3W7A== X-Received: by 10.50.219.229 with SMTP id pr5mr1223631igc.64.1358353851984; Wed, 16 Jan 2013 08:30:51 -0800 (PST) Received: from [192.168.1.22] (adsl-074-245-040-156.sip.clt.bellsouth.net. [74.245.40.156]) by mx.google.com with ESMTPS id dc8sm5099430igb.15.2013.01.16.08.30.49 (version=TLSv1 cipher=RC4-SHA bits=128/128); Wed, 16 Jan 2013 08:30:50 -0800 (PST) User-Agent: Microsoft-MacOutlook/14.2.5.121010 Date: Wed, 16 Jan 2013 11:30:45 -0500 Subject: returning the number of rows output by a copy command from a function From: James Sharrett To: Message-ID: Thread-Topic: returning the number of rows output by a copy command from a function Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3441180649_28850193" X-Gm-Message-State: ALoCoQmWXhqWjbKXbTBta2C8pXxXJwKCwr4mCI2s68eymRODan3qtOlYlqqRlYl7N3CjmU9DwdCJ X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3441180649_28850193 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit 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 --B_3441180649_28850193 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable
I have a function that gener= ates a table of records and then a SQL statement that does a COPY into a tex= t file.  I want to return the number of records output into the text fi= le from my function.  The number of rows in the table is not necessaril= y the number of rows in the file due to summarization of data in the table o= n 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 an= d populate the table of records to export}


strSQL :=3D 'copy (select MyColumns from MyExportTable) to MyFile.csv w= ith CSV HEADER;';
Execute strSQL;

Return = 0;

end
$BODY$
  LANGU= AGE 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 :=3D 'copy (select MyColumns from MyExportTable) to MyFile.csv with CS= V HEADER;';
= Execute strSQL into export_count;

Return export_count;

This give me an error saying that I've tried to use the INTO sta= tement with a command that doesn't return data.

2.
strSQL :=3D 'copy (select MyColumns from MyExportTable)= to MyFile.csv with CSV HEADER;';
Execute strSQL;

Get diagnostics=  export_count =3D row_count;

This always returns zero.=

3.
strSQL :=3D '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

--B_3441180649_28850193--