Fix migrator.go with multiple schemas with same dataset

[ivan-garcia-santamaria]

• [x] Do only one thing
• [x] Non breaking API changes
• [x] Tested

What did this pull request do?

Fix migrator.go with multiple schemas with same dataset

User Case Description

If you have a database with multiple schemas with the same tables, when you run migrator, it executes this query:

SELECT description 
FROM pg_catalog.pg_description 
WHERE objsubid = (SELECT ordinal_position 
                  FROM information_schema.columns 
                  WHERE table_name = 'my-table' AND column_name = 'created_at') 
AND objoid = (SELECT oid 
              FROM pg_catalog.pg_class 
              WHERE relname = 'my-table' 
              AND relnamespace = (SELECT oid 
                                  FROM pg_catalog.pg_namespace 
                                  WHERE nspname = CURRENT_SCHEMA())
             )

and it is wrong because the first subquery return a multiple choice and query fails with this error:

gorm.io/driver/postgres@v1.0.8/migrator.go:249 ERROR: more than one row returned by a subquery used as an expression (SQLSTATE 21000); ERROR: more than one row returned by a subquery used as an expression (SQLSTATE 21000)

For example, with this database:

• DB
  • schemas
    • yoigo
      • tables
        • credentials
    • pepephone
      • tables
        • credentials
    • masmovil
      • tables
        • credentials
    • public
      • tables
        • credentials

The migrator fails because there are multiple schemas with the same credentials table.

The correct query is this, including table_schema=CURRENT_SCHEMA():

SELECT description 
FROM pg_catalog.pg_description 
WHERE objsubid = (SELECT ordinal_position 
                  FROM information_schema.columns 
                  WHERE table_schema=CURRENT_SCHEMA() and table_name = 'credentials' AND column_name = 'created_at') 
AND objoid = (SELECT oid 
              FROM pg_catalog.pg_class 
              WHERE relname = 'credentials' 
              AND relnamespace = (SELECT oid 
                                  FROM pg_catalog.pg_namespace 
                                  WHERE nspname = CURRENT_SCHEMA())
             )

[jinzhu]

Can you use the current_schema value from this one? https://github.com/go-gorm/postgres/blob/fe12bf34e00e7dc0557bdfe73993b82a71c5a7cf/migrator.go#L323

[ivan-garcia-santamaria]

But the wrong sentence is this:

SELECT ordinal_position 
                  FROM information_schema.columns 
                  WHERE table_name = 'my-table' AND column_name = 'created_at'

And It already has its schema. You can't use CurrentSchema because you can't set any schema