Search Postgresql Archives

Re: validate synatax

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 






On 10 December 2013 22:57, Peter Kroon <plakroon@xxxxxxxxx> wrote:
>Why do you want to do that?
I want to validate the SQL syntax and preferably in the browser using some kind of linter.

>You can always run it inside transaction and rollback at the end.
Sounds dangerous and will make the server very active because it is executing the SQL.

Yes, the SQL should be tested even if the syntax is correct.

So 2 things to look for:
- Syntax validator
- Check query logic, like does the table exist

Best,
Peter


2013/12/10 Szymon Guz <mabewlun@xxxxxxxxx>



On 10 December 2013 22:40, Peter Kroon <plakroon@xxxxxxxxx> wrote:
Hi,

How can I validate any query on PostgreSQL without executing the sql.
I was able with EXPLAIN to find some errors. However this only worked with a SELECT statement. When i tried to create a TABLE it would not run.
I do not wish to install external packages. Preferably use only default present features.


Any suggestions are welcome.

Best,
Peter

Why do you want to do that? Maybe just test it, there can be many more problems then just not executing query, the query logic could be bad, even if the syntax is correct. You can always run it inside transaction and rollback at the end.

regards,
Szymon



So it seems like you want to have the impossible thing:

Syntax validator - you can always run explain... it should validate the simple query syntax. However it will not work for more complicated syntax, like imagine a procedure which creates a string from its arguments, and this string is then evaluated as a query. There is no way to be sure that it works other than just executing the procedure.

Query logic - another thing not doable without real query execution. What you want is to have this query executed without execution. What's more, if this would be doable, it won't give you enough information, as information that the query runs now, will not mean that this query will run in the future when you will be running that for real.

I really don't know how to create a simple validator (without execution) which would say that the following query is OK:

begin; create table x(a int); insert into x(a) values (10); end;

This would simply be as complicated as the database itself, and I'm sure that if I had to implement such a validator, I would just finish with embedding the query in a transaction rolled back at the end, and run it on some test database.

regards,
Szymon

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux