Thread: How to evaluate if a query is correct?

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';

Greetings
__________________________________________________
"Todos el 12 de Septiembre con una Cinta Amarilla"
FIN A LA INJUSTICIA, LIBERENLOS YA!!
http://www.antiterroristas.cu
                                    
http://justiciaparaloscinco.wordpress.com

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.

On 09/19/2013 12:13 AM, 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';
>
> Greetings
> __________________________________________________
> "Todos el 12 de Septiembre con una Cinta Amarilla"
> FIN A LA INJUSTICIA, LIBERENLOS YA!!
> http://www.antiterroristas.cu http://justiciaparaloscinco.wordpress.com
>
>
Is this an academic exercise or are you actually planning on checking
sql then executing it if it's ok (according to your function).  I love
plpgsql but I don't think it's the best option for, just to get started,
parsing the incoming sql text - that would be one nasty regexp :)

rjs

lup wrote
> On 09/19/2013 12:13 AM, 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...
>
> Is this an academic exercise or are you actually planning on checking
> sql then executing it if it's ok (according to your function).  I love
> plpgsql but I don't think it's the best option for, just to get started,
> parsing the incoming sql text - that would be one nasty regexp :)

There isn't a need to parse the text; the internal parser will do that for
you - you just need to make sure that the executor never gets ahold of the
resultant execution plan.

David J.





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