Re: [SQL] returning the number of rows output by a copy command from a function

2013-01-16 Thread Rob Sargent 

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

Re: [SQL] returning the number of rows output by a copy command from a function

2013-01-16 Thread Adrian Klaver 

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

Re: [SQL] returning the number of rows output by a copy command from a function

2013-01-16 Thread Misa Simic 
Hi

Maybe:


1.

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

strSQL := 'Select count(*) from (select MyColumns from MyExportTable) t';
Execute strSQL into export_count;

Return export_count;


Kind Regards,

Misa

On Wednesday, January 16, 2013, 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

Re: [SQL] returning the number of rows output by a copy command from a function

2013-01-16 Thread James Sharrett 
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 robjsarg...@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

Re: [SQL] returning the number of rows output by a copy command from a function

2013-01-16 Thread James Sharrett 
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.kla...@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.kla...@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

Re: [SQL] returning the number of rows output by a copy command from a function

2013-01-16 Thread Pavel Stehule 
2013/1/16 James Sharrett jsharr...@tidemark.net:
 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?


not yet

it is fixed in 9.3

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7ae1815961c635fd1a6fe72acb89fdef741a45a8

Regards

Pavel Stehule


 Thanks in advance,
 James



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] returning the number of rows output by a copy command from a function

2013-01-16 Thread Adrian Klaver 

On 01/16/2013 09:16 AM, James Sharrett wrote:

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.



Execute strSQL into export_count;

What is export_count DECLAREd as?


--
Adrian Klaver
adrian.kla...@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

Re: [SQL] returning the number of rows output by a copy command from a function

2013-01-16 Thread James Sharrett 
Integer



On 1/16/13 12:21 PM, Adrian Klaver adrian.kla...@gmail.com wrote:

On 01/16/2013 09:16 AM, James Sharrett wrote:
 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.


Execute strSQL into export_count;

What is export_count DECLAREd as?


-- 
Adrian Klaver
adrian.kla...@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

Re: [SQL] returning the number of rows output by a copy command from a function

2013-01-16 Thread Adrian Klaver 

On 01/16/2013 09:26 AM, James Sharrett wrote:

Integer



Well copy is returning a string, so try changing the type. You will have 
to parse that string for the count.


--
Adrian Klaver
adrian.kla...@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

Re: [SQL] returning the number of rows output by a copy command from a function

2013-01-16 Thread Misa Simic 
The other option would be to create temp table...

Execute dynamic sql to fil temp table

Copy from temp table - copy will return number of rowsx (not inside
execute...)

Drop temp

Kind regards,

Misa

On Wednesday, January 16, 2013, Misa Simic wrote:

 I meant the count from the same query as for copy command what actually go
 to file... Not count rows from table...

 But i agree could be slow...

 Cheers,

 Misa



 On Wednesday, January 16, 2013, James Sharrett wrote:

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