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
andgives 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
withinthe script. If, instead of opening in Excel, I save the file and give it a name, I have a csv file to open. But
thefile 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.
> 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@townofchapelhill.org
> Sparking Curiosity. Inspiring Learning. Creating Connections.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
