On 06/29/2018 12:53 PM, Adrian Klaver wrote:
On 06/29/2018 09:29 AM, Tracy Babiasz wrote:
Hi Adrian. Thanks for the response. I'm not sure how else to explain
it. The report executes fine in the query tool and gives me about 300
lines in data output. It even appears to export fine. I click on the
download button and open in Excel, but I get the statements I copied
below. The statement is slightly different if I remove the comment
statements within the script. If, instead of opening in Excel, I save
the file and give it a name, I have a csv file to open. But the file
is only 1KB, and if you open it, you see the statements I copied
below, rather than the results of the report.
Does that make sense?
Well I can replicate it so, yes.
Using pgAdmin4 3.1.
My 'report':
DROP TABLE IF EXISTS tmp_test;
CREATE table tmp_test(id integer, fld_1 varchar);
select * from cell_per;
The error I get when doing the export:
postgres-2018-06-29 12:39:16.349 PDT-0ERROR: syntax error at or near
"DROP" at character 55
postgres-2018-06-29 12:39:16.349 PDT-0STATEMENT: DECLARE
"CURSOR:CONN:8564986" CURSOR WITHOUT HOLD FOR DROP TABLE IF EXISTS
tmp_test;
CREATE table tmp_test(id integer, fld_1 varchar);
select * from cell_per;
Looks like pgAdmin4 is running all the queries using a declared
cursor(https://www.postgresql.org/docs/10/static/sql-declare.html). This
fails because the CURSOR can only take a SELECT or VALUES as the query.
The DROP TABLE will not work with a CURSOR.
To me it looks like a bug. I would file an issue here:
https://redmine.postgresql.org/projects/pgadmin4
You will need a Postgres community account to access the issue tracker.
If you do not have one there will be instructions on how to set one up
when you click on the link above.
For now what seems to work is to open another tab and then open another
Query Tool. In that Tool do the DROP TABLE/CREATE TABLE and then in the
other Tool do the SELECT and export from there.
After I posted the above I wondered how that could be as they are
different sessions and we are dealing with a temporary table. Then I
realized my example did not use a temporary table. So if you are going
to use the above then you will need to use a 'permanent' table.
Tracy Babiasz
Acquisitions and Collections Manager
Chapel Hill Public Library
100 Library Drive Chapel Hill, NC 27514
Phone: 919-969-2032 |Fax: 919-968-2838
tbabiasz@xxxxxxxxxxxxxxxxxxxx
Sparking Curiosity. Inspiring Learning. Creating Connections.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
