syntax error for drop, create view, etc

[artemisart]

Hi, thanks for this extension ! If I understood it well, it can check the syntax using EXPLAIN, but it executes it for every statement instead of only the following:

SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, CREATE TABLE AS, or CREATE MATERIALIZED VIEW AS

Reference : https://www.postgresql.org/docs/10/sql-explain.html

Would it be possible to only execute EXPLAIN for these statements ? Currently it shows syntax errors for valid DROPs, CREATE OR REPLACE VIEW etc Thank you !

[Borvik]

Yeah, this is a known shortcoming.

A "simple" way would be to just check if the statement starts with a valid/invalid statement type, but could also be unreliable for different versions of postgres.

I'm still attempting a parsing engine that can skip the explain altogether - which could also then validate statements EXPLAIN cannot - but it is extremely slow going (finding a decent parsing engine = hard, building = harder, doing it so it handles multiple syntaxes based on version = ... yeah).

[purge]

@Borvik have you looked at https://github.com/zhm/pg-query-parser which uses postgres built in raw_parser underneath.

[Borvik]

I'm not sure I've see that particular one, but I did see a similar one for Ruby and Python. I considered trying to roll my own that encapsulated the native C libraries that Postgres uses to use the native parsing engine (which is what this does).

The trick with that is, is that is using the 9.4 parsing engine - and locked to it. There is no switching between versions. Using the EXPLAIN query allows more version flexibility (for now) at the expense of certain types of queries.

My goal with the parser, would be something a bit dynamic. By pulling the tables/functions from the database I could possibly augment a parser to be semi-version flexible. It's the parser that's the hard part.

[ksemba]

@Borvik I love the extension - super helfpful! But if some of the SQL statements (I'm using DROP and TIMESTAMP [TimescaleDB on top of Postgres] at the moment) show a syntax error, VS code shows the file and the whole project in error state. So even though there is no issue with the overall project, it "appears" to be broken (colored in red).

any chance you are going to enhance this? Is there a workaround (like using another VS code extension to perform syntax checking on *.psl files but still using your extension for everything else)?