Improved auto suggestions (scoring) for schemas and table names possible?

[Donnerstagnacht]

Feature request

Is your feature request related to a problem? Please describe.

When using the auto suggestions in vs-code, the suggested items after a FROM clause could be improved.

For writing DDL I mosly use AI these days. In my case, the auto suggestion use case which would benefit my productivity the most would be better auto suggestions for schemas and tables since I often fine tune queries by hand.

While not understanding the details from https://supabase.com/blog/postgres-language-server
, I feel that as a user I might feel the impact of the lazy loading, the scoring algorithm and the incompleted statements.

So I would like to describe 3 experiences or improvement wishes from my point of view.

Schema suggestions after FROM clause

Right now, if I type SELECT id FROM or SELECT id FROM g I do not receive schema suggestions. In the first case the AI already predicts a possible schema and table.

I think my preferred version would be that a list of all possible schemas (or schemas and tables) is displayed here. I personally mention schemas always explicitly which means that in my case a schema always follows a FROM keyword.

In general, I feel that the tool never detects or suggest schema names.

Does the lazy loading become an issue here? Would be an approach to load all schema names once the extension is loaded? Would be useful to increase the weight of schemas and tables after FROM?

Adding schema automatically when selecting table

If I type SELECT id FROM group_c it suggests the correct table, but if I choose it from the autosuggestions it does not add the schema name and reports an error.

My preferred version would be that the tool automatically adds the correct schema.

Suggesting tables after .

Since I think that it does not recognize schemas, I type the schema and add a point. Now it would ideally suggest a table from that schema. However, it seems to load infinitely long.

However, once I add a semicolon, it suggests the correct groups.

Would it be possible to somehow interprete the . and , as semicolon or to trigger the autosuggestions? What does the loading mean? Is it a performance issue? Would it make sense to exclude some schemas from suggestions?

Column suggestion if statement is completed

Often I type SELECT * first or update the returned columns by hand. So I would edit a completed statement like SELECT id FROM groups_hidden.groups;

When I want to add a second column, the tool suggests columns which are not possible in this context because the table is already chosen. I think it could be more restrictive here:

If I type a column name first and add a comma (,), then it suggests columns which are not relevant in my opinion.

In both cases, I would prefer that only columns from the selected table (or tables in joins) are shown. Especially in the last case, it is very likely, that a user would like to choose another column from the current table. Could the weight of columns from the current table be increased?

I was not sure where to add the request (here or in the main repository) and if there is already a technical issue about that in the main repository. If they are, maybe these use cases could be test cases. I believe that these should be common editing workflows.

PS: Thanks for your work! It works great especially to check the quality of AI-generated code.

[juleswritescode]

That's very useful input, thank you :)

Actually, completing after a FROM didn't even work properly - this will hopefully fix that.

Quick aside on how it works: When the postgres-schema information (available columns, tables, functions…) is first needed, it is loaded and then cached. So you should only experience a slight delay on the first auto-complete, if at all. When you use the EXECUTE STATEMENT code action, the schema cache should be refreshed. When you update the DDL, we currently do not notice or react to that.

You wrote that after a FROM, we should first suggest schema names – I really like that idea. I'll try to adopt it.

Regarding the rest: Yes, fully agree! If it doesn't work the way you described, that's not intended. The whole completions thing doesn't work as well as I'd like to work, but I'll fix that in the coming weeks. :)

[Donnerstagnacht]

The from seems to work better but the other mentioned use cases are not optimal yet. You can close it or I can split it up in multiple issues.

I keep it open until then.

[Donnerstagnacht]

When you update the DDL, we currently do not notice or react to that

What exactly do you mean by this? Do I have to restart the server if I add a new table to the database?

Thanks for the schemas! ☺️

How do you plan to work on the autocomplete/scoring tweaks? Shall users add ideas via discussions in the psl repo or via issues?

[juleswritescode]

What exactly do you mean by this? Do I have to restart the server if I add a new table to the database?

Yes, exactly! We're loading all the database-related data into cache once.

How do you plan to work on the autocomplete/scoring tweaks? Shall users add ideas via discussions in the psl repo or via issues?

For now, I'm fumbling around with some statements and see if I can adjust the completions so they feel intuitive and useful.
But it will be trickier to find issues once we're beyond the simple stuff, so yeah, please report both wishes/bugs and so on. :)

I'd say go for discussions first, but if it's something "buggy" like suggesting a column that doesn't exist on the queried table, report it as an issue 👍

[juleswritescode]

another update, release 0.6.0 has much improved suggestions 👍

[Donnerstagnacht]

Wow! Really cool :) Love it! The plain query editing feels great now!

I think you also implemented everything I have mentioned above. Thanks!