Can't create temporary table inside a query

[dfffffff]

When I try to create a temporary table inside a query, I get "no such table" error.

e.g.:

CREATE TEMP TABLE tmptable(val INTEGER);
INSERT INTO tmptable VALUES(1);

Such table might be needed to hold values for further SELECT thereafter.

[fr-ser]

That is an interesting observation indeed. I think this might be due to the difference between "Queries" and "Statements" in the SQL go interface. I don't know how to solve this easily, yet, but I also don't know if it is worth investigating.

Grafana is not meant to be an SQL editor of the database (there are far better ones like dbeaver out there). Are CTEs enough for your use case?

WITH temptable (val1, val2) AS (
-- transform join or whatever
SELECT * from sine_wave
)
SELECT * FROM temptable

[fr-ser]

I might investigate later a bit more in-depth about the issue, just out of curiosity, but I would like to clarify first if this is necessary in the end to "fix" in the plugin at all.

[dfffffff]

I have made a crude hack for my use case (my first experience with go),

diff --git a/pkg/query.go b/pkg/query.go
index 1350570..a224578 100644
--- a/pkg/query.go
+++ b/pkg/query.go
@@ -6,6 +6,7 @@ import (
    "fmt"
    "strconv"
    "time"
+   "strings"

    "github.com/grafana/grafana-plugin-sdk-go/backend"
    "github.com/grafana/grafana-plugin-sdk-go/backend/log"
@@ -224,9 +225,27 @@ func fetchData(dbPath string, qm queryModel) (columns []*sqlColumn, err error) {
        return columns, err
    }

-   rows, err := db.Query(qm.QueryText)
+   execText := ""
+   queryText := qm.QueryText
+   if queryText[:1] == "{" {
+       i := strings.Index(queryText, "\n}")
+       if i >= 0 {
+           execText = queryText[1:i]
+           queryText = queryText[i+2:]
+       }
+   }
+
+   if execText != "" {
+       _, err = db.Exec(execText)
+       if err != nil {
+           log.DefaultLogger.Error("Could not execute", "query", execText, "err", err)
+           return columns, err
+       }
+   }
+
+   rows, err := db.Query(queryText)
    if err != nil {
-       log.DefaultLogger.Error("Could execute query", "query", qm.QueryText, "err", err)
+       log.DefaultLogger.Error("Could not query", "query", queryText, "err", err)
        return columns, err
    }
    defer rows.Close()

It execute statements enclosed inside { } at the begining of the query with Exec(), the remaining is using Query() as usual.

e.g.:

{
    PRAGMA temp_store = MEMORY;
    CREATE TEMP table tmptable(val INTEGER);
    INSERT INTO tmptable VALUES(.....);
}
SELECT .....
FROM maintable
WHERE ts >= (${__from}/1000) AND ts <= (${__to}/1000)
AND val = (SELECT val FROM tmptable)
.....

[fr-ser]

I don't see the benefit. First the parsing with the {...} is intransparent and a custom solution, that nobody will expect.

But why do you need this in your Grafana dashboards at all?

[dfffffff]

This speed up the query, just wondering if it was possible to do it with sqlite, because PostgreSQL plugin can.

[dfffffff]

I updated my sqlite3 version (had v3.8.2), now I have the WITH clause, things are a little faster than using a temporary table. So thank you for your help and your great work. This plugin should definitely be included with Grafana.