Re: How to evaluate if a query is correct?

Juan Daniel Santana Rodés wrote
> Hi my friends...
> I wrote in the last post a question similiar to this. But in this post I
> clarify better the previous question.
> I need know how to evaluated if a query is correct without execute it.
> When I say if a query is correct, is that if I run the query, it did not
> throw an exception.
> For example...
>
> create or replace function is_correct(query text) returns boolean as
> $body$
> Declare
> Begin
> -- Here I check if the query is correct, but I can't execute this
> query, because it may make changes in the data base
> End;
> $body$
> language 'plpgsql';

Some errors only manifest themselves if there is data (like a division by
zero error). Others are dependent on who is running the query. Maybe it
uses DBLink/FDW and the target is unavailable/missing. These are impossible
to check for and indeed change so that the same exact query can fail or
succeed depending on external state.

A savepoint/rollback option is possible though some queries can cause
irreversible actions to occur.

As mentioned before you can try something like:

EXECUTE 'EXPLAIN ' || query; RETURN true; EXCEPTION ... RETURN false;

Try and make the concept work and post a specific question, with your
attempt, if you cannot. Or wait and see if someone else gives you a working
answer. Either way there are limitations to this and since you provide no
context as to how this function will be used it is hard to know whether
those limitations matter. Most people here try to give more than just
"here's how you do it" answers since even if something technically works it
may not be doing what you think.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-evaluate-if-a-query-is-correct-tp5771568p5771607.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.