bug: goctl pg gen will extract all fields when the same table name exists in different schemas

[pleamon]

Describe the bug goctl pg gen will extract all fields when the same table name exists in different schemas

To Reproduce Steps to reproduce the behavior, if applicable:

1. The code is

2. create table orders.order

3. create view orders.order

   goctl model pg datasource -url ... -s "orders" -t "order" -c --strict

4. The error is

   db:orders, table:order_invoice, joint primary key is not supported

Expected behavior A clear and concise description of what you expected to happen.

Screenshots If applicable, add screenshots to help explain your problem.

Environments (please complete the following information):

• OS: [e.g. Linux]
• go-zero version [e.g. 1.2.1]
• goctl version [e.g. 1.2.1, optional]

More description Add any other context about the problem here.

Problem code

   func (m *PostgreSqlModel) FindColumns(schema, table string) (*ColumnData, error) {
    querySql := `select t.num,t.field,t.type,t.not_null,t.comment, c.column_default, identity_increment
from (
         SELECT a.attnum AS num,
                c.relname,
                a.attname     AS field,
                t.typname     AS type,
                a.atttypmod   AS lengthvar,
                a.attnotnull  AS not_null,
                b.description AS comment
         FROM pg_class c,
              pg_attribute a
                  LEFT OUTER JOIN pg_description b ON a.attrelid = b.objoid AND a.attnum = b.objsubid,
              pg_type t
         WHERE c.relname = $1
           and a.attnum > 0
           and a.attrelid = c.oid
           and a.atttypid = t.oid
         GROUP BY a.attnum, c.relname, a.attname, t.typname, a.atttypmod, a.attnotnull, b.description
         ORDER BY a.attnum) AS t
         left join information_schema.columns AS c on t.relname = c.table_name 
        and t.field = c.column_name and c.table_schema = $2`

    var reply []*PostgreColumn
    err := m.conn.QueryRowsPartial(&reply, querySql, table, schema)
    if err != nil {
        return nil, err
    }

       ...
}

Fix code Update the querySql, Increase judgment on schema

select t.num,t.field,t.type,t.not_null,t.comment, c.column_default, identity_increment
from (
         SELECT a.attnum AS num,
                c.relname,
                a.attname     AS field,
                t.typname     AS type,
                a.atttypmod   AS lengthvar,
                a.attnotnull  AS not_null,
                b.description AS comment,
                (c.relnamespace::regnamespace)::varchar AS schema_name
         FROM pg_class c,
              pg_attribute a
                  LEFT OUTER JOIN pg_description b ON a.attrelid = b.objoid AND a.attnum = b.objsubid,
              pg_type t
         WHERE c.relname = $1
           and a.attnum > 0
           and a.attrelid = c.oid
           and a.atttypid = t.oid
         GROUP BY a.attnum, c.relname, a.attname, t.typname, a.atttypmod, a.attnotnull, b.description, c.relnamespace::regnamespace
         ORDER BY a.attnum) AS t
         left join information_schema.columns AS c on t.relname = c.table_name and t.schema_name = c.table_schema
        and t.field = c.column_name
        where c.table_schema = $2

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.