pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreturning the number of rows output by a copy command from a function
13+ messages / 6 participants
[nested] [flat]
* returning the number of rows output by a copy command from a function
@ 2013-01-16 16:30 James Sharrett <jsharrett@tidemark.net>
0 siblings, 4 replies; 13+ messages in thread
From: James Sharrett @ 2013-01-16 16:30 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-16 16:36 Rob Sargent <robjsargent@gmail.com>
parent: James Sharrett <jsharrett@tidemark.net>
3 siblings, 1 reply; 13+ messages in thread
From: Rob Sargent @ 2013-01-16 16:36 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-16 16:42 Adrian Klaver <adrian.klaver@gmail.com>
parent: James Sharrett <jsharrett@tidemark.net>
3 siblings, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2013-01-16 16:42 UTC (permalink / raw)
To: James Sharrett <jsharrett@tidemark.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-16 16:55 Misa Simic <misa.simic@gmail.com>
parent: James Sharrett <jsharrett@tidemark.net>
3 siblings, 0 replies; 13+ messages in thread
From: Misa Simic @ 2013-01-16 16:55 UTC (permalink / raw)
To: James Sharrett <jsharrett@tidemark.net>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-16 17:13 James Sharrett <jsharrett@tidemark.net>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: James Sharrett @ 2013-01-16 17:13 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-16 17:16 James Sharrett <jsharrett@tidemark.net>
parent: Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: James Sharrett @ 2013-01-16 17:16 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-16 17:19 Pavel Stehule <pavel.stehule@gmail.com>
parent: James Sharrett <jsharrett@tidemark.net>
3 siblings, 0 replies; 13+ messages in thread
From: Pavel Stehule @ 2013-01-16 17:19 UTC (permalink / raw)
To: James Sharrett <jsharrett@tidemark.net>; +Cc: pgsql-sql
2013/1/16 James Sharrett <jsharrett@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=7ae1815961c635fd1a6fe72acb89fdef74...
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-16 17:21 Adrian Klaver <adrian.klaver@gmail.com>
parent: James Sharrett <jsharrett@tidemark.net>
1 sibling, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2013-01-16 17:21 UTC (permalink / raw)
To: James Sharrett <jsharrett@tidemark.net>; +Cc: pgsql-sql
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.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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-16 17:26 James Sharrett <jsharrett@tidemark.net>
parent: Adrian Klaver <adrian.klaver@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: James Sharrett @ 2013-01-16 17:26 UTC (permalink / raw)
To: pgsql-sql
Integer
On 1/16/13 12:21 PM, "Adrian Klaver" <adrian.klaver@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.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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-16 17:29 Adrian Klaver <adrian.klaver@gmail.com>
parent: James Sharrett <jsharrett@tidemark.net>
0 siblings, 0 replies; 13+ messages in thread
From: Adrian Klaver @ 2013-01-16 17:29 UTC (permalink / raw)
To: James Sharrett <jsharrett@tidemark.net>; +Cc: pgsql-sql
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.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
^ permalink raw reply [nested|flat] 13+ messages in thread
* returning the number of rows output by a copy command from a function
@ 2013-01-16 17:33 Misa Simic <misa.simic@gmail.com>
parent: James Sharrett <jsharrett@tidemark.net>
0 siblings, 1 reply; 13+ messages in thread
From: Misa Simic @ 2013-01-16 17:33 UTC (permalink / raw)
To: James Sharrett <jsharrett@tidemark.net>; +Cc: pgsql-sql
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" <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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-16 17:46 Misa Simic <misa.simic@gmail.com>
parent: Misa Simic <misa.simic@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Misa Simic @ 2013-01-16 17:46 UTC (permalink / raw)
To: James Sharrett <jsharrett@tidemark.net>; +Cc: pgsql-sql
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" <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
>>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: returning the number of rows output by a copy command from a function
@ 2013-01-17 10:54 Jasen Betts <jasen@xnet.co.nz>
parent: James Sharrett <jsharrett@tidemark.net>
1 sibling, 0 replies; 13+ messages in thread
From: Jasen Betts @ 2013-01-17 10:54 UTC (permalink / raw)
To: pgsql-sql
On 2013-01-16, James Sharrett <jsharrett@tidemark.net> 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.
look into "get diagnostics"
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2013-01-17 10:54 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-16 16:30 returning the number of rows output by a copy command from a function James Sharrett <jsharrett@tidemark.net>
2013-01-16 16:36 ` Rob Sargent <robjsargent@gmail.com>
2013-01-16 17:13 ` James Sharrett <jsharrett@tidemark.net>
2013-01-16 17:33 ` Misa Simic <misa.simic@gmail.com>
2013-01-16 17:46 ` Misa Simic <misa.simic@gmail.com>
2013-01-16 16:42 ` Adrian Klaver <adrian.klaver@gmail.com>
2013-01-16 17:16 ` James Sharrett <jsharrett@tidemark.net>
2013-01-16 17:21 ` Adrian Klaver <adrian.klaver@gmail.com>
2013-01-16 17:26 ` James Sharrett <jsharrett@tidemark.net>
2013-01-16 17:29 ` Adrian Klaver <adrian.klaver@gmail.com>
2013-01-17 10:54 ` Jasen Betts <jasen@xnet.co.nz>
2013-01-16 16:55 ` Misa Simic <misa.simic@gmail.com>
2013-01-16 17:19 ` Pavel Stehule <pavel.stehule@gmail.com>
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