Get Table names from parsed query

[Hassnain-Alvi]

Hi.
How can i get table names from the query parser?
in pg_query its mentioned as follows :

PgQuery.parse("SELECT ? FROM x JOIN y USING (id) WHERE z = ?").tables

but when i do it in pg_query_go as follows:
tables := pg_query.Parse("SELECT ? FROM x JOIN y USING (id) WHERE z = ?").tables

it returns an error.
Can you please post an example for this?
Thanks

[elliotcourant]

If I remember correctly, all the table names are in the parse tree at the same type. I have a method (albeit ugly) that uses reflect to return a list of table names based on that premise.

import (
	"fmt"
	"github.com/readystock/golinq"
	"github.com/lfittl/pg_query_go/nodes"
	"reflect"
	"strings"
)

func GetTables(stmt interface{}) []string {
	tables := make([]string, 0)
	linq.From(examineTables(stmt, 0)).Distinct().ToSlice(&tables)
	return tables
}

func examineTables(value interface{}, depth int) []string {
	args := make([]string, 0)
	print := func(msg string, args ...interface{}) {
		fmt.Printf("%s%s\n", strings.Repeat("\t", depth), fmt.Sprintf(msg, args...))
	}

	if value == nil {
		return args
	}

	t := reflect.TypeOf(value)
	v := reflect.ValueOf(value)

	if v.Type() == reflect.TypeOf(pg_query.RangeVar{}) {
		rangeVar := value.(pg_query.RangeVar)
		args = append(args, *rangeVar.Relname)
	}

	switch t.Kind() {
	case reflect.Ptr:
		if v.Elem().IsValid() {
			args = append(args, examineTables(v.Elem().Interface(), depth+1)...)
		}
	case reflect.Array, reflect.Chan, reflect.Map, reflect.Slice:
		depth--
		if v.Len() > 0 {
			print("[")
			for i := 0; i < v.Len(); i++ {
				depth++
				print("[%d] Type {%s} {", i, v.Index(i).Type().String())
				args = append(args, examineTables(v.Index(i).Interface(), depth+1)...)
				print("},")
				depth--
			}
			print("]")
		} else {
			print("[]")
		}
	case reflect.Struct:
		for i := 0; i < t.NumField(); i++ {
			f := t.Field(i)
			print("[%d] Field {%s} Type {%s} Kind {%s}", i, f.Name, f.Type.String(), reflect.ValueOf(value).Field(i).Kind().String())
			args = append(args, examineTables(reflect.ValueOf(value).Field(i).Interface(), depth+1)...)
		}
	}
	return args
}

It probably isn't the best way to do it, but it works.

[elliotcourant]

Here is an example test to help.

import (
	"github.com/lfittl/pg_query_go"
	pg_query2 "github.com/lfittl/pg_query_go/nodes"
	"github.com/stretchr/testify/assert"
	"testing"
)

var (
	tableTestQueries = []struct {
		Query  string
		Tables []string
	}{
		{
			Query:  "SELECT $1::text;",
			Tables: []string{},
		},
		{
			Query:  "SELECT e.typdelim FROM pg_catalog.pg_type t, pg_catalog.pg_type e WHERE t.oid = $1 and t.typelem = e.oid",
			Tables: []string{"pg_type"},
		},
		{
			Query:  "SELECT e.typdelim FROM pg_catalog.pg_type t, pg_catalog.pg_type e WHERE t.oid = $1 and t.typelem = e.oid AND $2=$3",
			Tables: []string{"pg_type"},
		},
		{
			Query:  "SELECT e.typdelim FROM pg_catalog.pg_type t, pg_catalog.pg_type e WHERE t.oid = $1 and t.typelem = e.oid AND $2=$1",
			Tables: []string{"pg_type"},
		},
		{
			Query:  "SELECT products.id FROM products JOIN types ON types.id=products.type_id",
			Tables: []string{"products", "types"},
		},
		{
			Query:  "SELECT products.id FROM products JOIN types ON types.id=products.type_id WHERE products.id IN (SELECT id FROM other)",
			Tables: []string{"products", "types", "other"},
		},
		{
			Query:  "INSERT INTO products (id) VALUES(1);",
			Tables: []string{"products"},
		},
		{
			Query:  "UPDATE variations SET id=4 WHERE id=3;",
			Tables: []string{"variations"},
		},
	}
)

func Test_GetTables(t *testing.T) {
	for _, item := range tableTestQueries {
		parsed, err := pg_query.Parse(item.Query)
		if err != nil {
			t.Error(err)
			t.FailNow()
		}

		stmt := parsed.Statements[0].(pg_query2.RawStmt).Stmt

		tableCount := GetTables(stmt)

		assert.Equal(t, item.Tables, tableCount, "number of tables does not match expected")
	}
}

With some small modifications to the method I posted in my last comment, it could also include the schema in the table name array.

[Hassnain-Alvi]

Thanks it worked

[SerialVelocity]

That seems to fail with aliases.
e.g.
WITH a AS (SELECT * FROM tmp) SELECT * FROM a