Named Query Not Working as expected

Ignition
1

In a PostgreSQL database I have a table that contains seat counts.
The rows have values of Current Hour, Available Seats, & Empty Seats
Curr_Hour Available Empty
9 32 3
9 32 4
10 32 8
10 32 0
10 32 4

I am calculating the sum of Filled Seats form the Available & Empty Seat values (sums).
My problem is that I need to get two rows of data to populate a Pie Chart.
Those rows would consist of two columns:
SeatType todayTotal
Filled ###
Empty ###

In pgAdmin I have written a query that returns what I need:

DROP TABLE IF EXISTS util_daily_totals;
CREATE TEMP TABLE util_daily_totals
(
	seatType	varchar(7),
	todayTotal	integer
)
ON COMMIT DELETE ROWS;

INSERT INTO util_daily_totals
		(seatType, todayTotal)
	VALUES('Empty', 
			(SELECT e."Empty" 
				FROM
					(SELECT
						SUM(seats_empty) as "Empty"
					FROM  util_hourly 
					WHERE source = 'Track 902' 
						and curr_date = DATE('2020-06-16')) as e));

INSERT INTO util_daily_totals
		(seatType, todayTotal)
	VALUES('Filled', 
			(SELECT f."Filled"   
				FROM
					(SELECT
						SUM(seats_avail) as "Available", 
						SUM(seats_empty) as "Empty", 
						SUM((seats_avail - seats_empty)) as "Filled"
					FROM  util_hourly 
					WHERE source = 'Track 902'
						and curr_date = DATE('2020-06-16')) as f));

SELECT * FROM util_daily_totals;

The results look like this:
seatType todayTotal
“Empty” 49
“Filled” 79

When I paste this query into my Named Query it dose not return any values.
I do get an error dialog with the following error:

com.inductiveautomation.ignition.client.gateway_interface.GatewayException: org.postgresql.util.PSQLException: No results were returned by the query.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:339)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:313)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:266)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:915)
	at com.inductiveautomation.ignition.designer.db.namedquery.workspace.NamedQueryTestingPanel$1$1.doInBackground(NamedQueryTestingPanel.java:313)
	at com.inductiveautomation.ignition.designer.db.namedquery.workspace.NamedQueryTestingPanel$1$1.doInBackground(NamedQueryTestingPanel.java:288)
	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.lang.Exception: org.postgresql.util.PSQLException: No results were returned by the query.
	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:413)
	at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:199)
	at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:117)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.NamedQueryFunctions.executeNamedQuery(NamedQueryFunctions.java:67)
	at jdk.internal.reflect.GeneratedMethodAccessor164.invoke(null)
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)
	at java.lang.reflect.Method.invoke(null)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:225)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:412)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)
	at org.eclipse.jetty.servlet.ServletHolder$NotAsyncServlet.service(ServletHolder.java:1391)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:760)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:547)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:590)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1607)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1297)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:485)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1577)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1212)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
	at org.eclipse.jetty.server.Server.handle(Server.java:500)
	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:383)
	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:547)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:375)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:270)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:388)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:806)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:938)
	at java.lang.Thread.run(null)

Ignition v8.0.12 (b2020042115)
Java: Azul Systems, Inc. 11.0.6

Are there differences in how the query is handled on the Ignition side than on the PostgreSQL side?
How do I get this to work?

2

Anyone? Bueller? Bueller?..

3

Have you tried this in the Query Browser?

4

Looks like this would be better off being a stored procedure… Then again, I guess a named query can be seen as ignition’s version of this :thinking:
I don’t know about postgre, but in sql server you’d need GO statements between all of your independent queries

5

I tried it at your suggestion, and no, it does not work there either, though I get a different error message.
The message tab of the error dialog states the following:

Error running query: "...Echo of Qeury...": A result was returned when none was expected.

The Details tab contains the following:

GatewayException: SQL error for "...Echo of Query...": A result was returned when none was expected.
	caused by PSQLException: A result was returned when none was expected.

Ignition v8.0.12 (b2020042115)
Java: Azul Systems, Inc. 11.0.6[Query Error in Query Browser.txt|attachment](upload://qHhcSj9WLFctYI8du3ZAww8ATNu.txt) (6.4 KB)

I have attached the full error copied from the error dialog.

I’m stumped as I am not really familiar with PostgreSQL. The fact that it runs as expected in PostgreSQL pgAdmin query window but not in Ignitions Query Browser or Named Query makes me lean toward it not being a PostgreSQL issue.

6

I’m trying to build a PostgreSQL function, but it is not exactly like building a stored procedure in MSSQL unfortunately.