[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to evaluate if a query is correct?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to evaluate if a query is correct?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to evaluate if a query is correct?
Hello you can use a PREPARE statement. Pavel 2013/9/19 Rob Sargentg robjsarg...@gmail.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.comhttp://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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/**mailpref/pgsql-generalhttp://www.postgresql.org/mailpref/pgsql-general