Has anyone else had issues with using FieldSchema with PostgreSQL databases? I'm getting a valid list of table names but then when I call FieldSchema I get a valid recordset back with zero fields.

I don't see anything in Feedback, but sometimes it's hard to tell.

It's not reporting nil for me, the recordset just has no records in it. Given your example above, flds is NOT nil but EOF and BOF are true so nothing is processed.

I've created a simple feedback report at Feedback Case #45023

I remember having some issues like this, and the need to get more informations than what Fieldschema was returning.
so I asked postgres directly about his schema like this, at the beginning of the app to store it localy in app properties.
this method is in a database subclass. "Alerte" method is like msgbox.

Private Sub postgresLoadFieldSchema(forceReload as Boolean = False, schemaName as String="public")
  ' --------------------------------------------------------------------------------
  ' lire toutes les fields d'une base PostgreSQL en direct
  ' --------------------------------------------------------------------------------
  
  dim ch as String
  dim rs2 as RecordSet
  
  if UBound(postgresField_table_name)<0 or forceReload then
    ' liste toutes les fields de la base et les stocke localement une seule fois
    ' table_name,column_name,ordinal_position,data_type,is_nullable,character_maximum_length
    ch = "SELECT table_name,column_name,ordinal_position,data_type,is_nullable,character_maximum_length,udt_name FROM information_schema.columns WHERE table_schema = '"
    ch = ch + schemaName +"' ORDER BY table_name,ordinal_position ASC"
    rs2 = SQLSelect( ch)
    if rs2<>nil then
      do
        postgresField_table_name.Append rs2.IdxField(1).StringValue
        postgresField_column_name.Append rs2.IdxField(2).StringValue
        postgresField_ordinal_position.Append rs2.IdxField(3).StringValue
        postgresField_data_type.Append rs2.IdxField(7).StringValue // udt_name est plus explicite
        postgresField_is_nullable.Append rs2.IdxField(5).StringValue
        postgresField_character_maximum_length.Append rs2.IdxField(6).StringValue
        rs2.MoveNext
      loop until rs2.EOF
    else
      Alerte "postgresLoadFieldSchema: rs2 is nil !"
    end if
  end if
  
End Sub

#45023 - serious issue , but I think you can work around:

SELECT attrelid::regclass, attnum, attname
FROM   pg_attribute
WHERE  attrelid = 'public.tbl_rel'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

schema = 'public' table = 'tbl_rel'

Bob, I can reproduce your issue if there are caps in the table name, e.g., "MyTable" instead of "mytable" or "my_table". Could that be it?

I can't find a way to force it when there are caps in the table name either. I've tried double-quotes and square brackets (not even sure if that would work normally) with no effect.

@Kem T Bob, I can reproduce your issue if there are caps in the table name, e.g., "MyTable" instead of "mytable" or "my_table". Could that be it?

Yeah, all of the table names in Chinook are camel case.

I'll see if I can play around with it a bit more today. Very frustrating.

@Joost R #45023 - serious issue , but I think you can work around:

SELECT attrelid::regclass, attnum, attname
FROM   pg_attribute
WHERE  attrelid = 'public.tbl_rel'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

schema = 'public' table = 'tbl_rel'

It's kind of funny, but this where I started on all of this. I had this query and it was giving me errors with a 'missing relationship' when clearly the tables are there. Starting to think that maybe having the mixed case table names is the root cause of a lot of issues.

@Norman P BINGO !
recreated chinook using the script as all lower case & away we go

@Norman P, do I need to submit a Feedback report for this?

The problem is, postgres itself will tend to lowercase table and column name identifiers. For example, if you have a table "my_table", you can issue SQL like this and it will work:

SELECT * FROM My_Table

On the other hand, if the table name is actually "My_Table", it won't because PSQL lowercases the identifier in the SQL. If you quote "My_Table" in the SQL, it will work in the latter case but not in the former.

Technically, the Xojo calls should have a way to use mixed case identifiers, but I can't think of a clean way to do that without breaking other code. Based on my research, Norman's recommendation is not only the way around the problem, but seems to be "best practice" when it comes to Postgres.

For your convenience, here is code to convert camel-case to snake_case:

Public Function CamelToSnake(Extends camel As String) as String
  if camel.Trim = "" then
    return ""
  end if
  
  dim hexedCamel as string = EncodeHex(camel)
  Static camelCache As New Dictionary
  dim r as string = camelCache.Lookup(hexedCamel, "")
  if r <> "" then
    return r
  end if
  
  static rx as RegEx
  if rx is nil then
    rx = new RegEx
    rx.SearchPattern = "(?mi-Us)\p{Lu}+(?=\p{Lu}\P{Lu}|\d|$)|\d+|^.[^\p{Lu}\d\r\n]*|\p{Lu}[^\p{Lu}\d\r\n]+"
  end if
  
  Dim result() As String
  dim match as RegExMatch = rx.Search(camel)
  while match isa RegExMatch
    result.Append match.SubExpressionString(0).Lowercase
    match = rx.Search
  wend
  
  r = Join(result, "_")
  dim squeezed as string = r
  do
    r = squeezed
    squeezed = squeezed.ReplaceAll("__", "_")
  loop until (r = squeezed)
  
  camelCache.Value(hexedCamel) = r
  
  Return r
End Function

We use it to match property names to table fields, so we can have in the database "table.some_field" and map it to the property "SomeField" in class "Table" pretty easily.

But here I was thinking as part of the initial "cleanup" to convert the camel-case names to snake-case without having to work through each one by hand.

I thought this sounded familiar so I spotlighted "Chinook Postgres" and found...

"Chinook_PostgreSql_lowercaseNoQuotes" - a BBEdit file I converted in the past :)