Thread: validate synatax

Peter Kroon <plakroon@gmail.com> writes:

> 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
aTABLE it would not run. 
> I do not wish to install external packages. Preferably use only default present features.
>
> http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT#PostgreSQL_EXPLAIN
>
> Any suggestions are welcome.


/yomama
=# create or replace function foo() returns void as $$create table foo()$$ language sql;
CREATE FUNCTION
/yomama
=# create or replace function foo() returns void as $$create table foo() bogus syntax$$ language sql;
ERROR:  syntax error at or near "bogus"
LINE 1: ...nction foo() returns void as $$create table foo() bogus synt...
                                                             ^
/yomama
=# select version();
                                           version
----------------------------------------------------------------------------------------------
 PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
(1 row)


>
> Best,
> Peter
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

On Wed, Dec 11, 2013 at 9:11 PM, Szymon Guz <mabewlun@gmail.com> wrote:
> 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.
Such a *query validator* needs to go through the database parser of
gram.y and in rewriter to check for some restrictions as well (maybe
some stuff in planner but I cannot recall what now), so you need to
minimize the execution time of the query in planner and executor for
DML/SELECT, and bypass utility execution for a DDL and a utility. You
could use the planner hook to generate a dummy plan that will 1) skip
the planner, 2) minimize the query execution and check only if the
query has a valid grammar for DML/SELECT, and the utility hook to skip
all the DDL/utility executions.

Regards,
--
Michael

you can use the transition.
eg:
begin;
creat table(...);
catch error if the statement not validated.
rollback;

jov