[GENERAL] Recursive function
Hi everyone... I have a problem. I am programming a recursive function in plpgsql language. This function use a cursor and when the function try to call the same function throw a exception that it say me that the cursor is using. My friends how I can to resolve this problem. Regards!! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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
[GENERAL] How to compare the results of two queries?
I am developing a task in which I need to know how to compare the results of two queries ... I thought about creating a procedure which both queries received by parameters respectively. Then somehow able to run queries and return if both have the same result. As a feature of the problem, both queries are selection. Here I leave a piece of code I want to do. create or replace function compare(sql1 character varying, sql2 character varying) returns boolean as $body$ Declare Begin --here in some way to run both queries and then compare End; $body$ language 'plpgsql'; I've been studying and I found that there EXECUTE but to use it, first you should have used PREPARE, and in this case the values of the parameters are already made inquiries. For example the execution of the function would be something like ... select compare('select * from table1', 'select * from table2'); For this case the result is false, then the queries are executed on different tables. Thanks in advance. Best regards from Cuba. __ 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 compare the results of two queries?
El 17/09/13 12:02, Igor Neyman escribió: -Original Message- From: Juan Daniel Santana Rodés [mailto:jdsant...@estudiantes.uci.cu] Sent: Tuesday, September 17, 2013 11:54 AM To: Igor Neyman Subject: Re: [GENERAL] How to compare the results of two queries? El 17/09/13 11:27, Igor Neyman escribió: create or replace function compare(sql1 character varying, sql2 character varying) returns boolean as $body$ Declare lCount int := 0; Begin EXECUTE 'SELECT COUNT(Res.*) FROM ( (' || sql1 || ' EXCEPT ' || sql2 || ') UNION (' || sql2 || ' EXCEPT ' || sql1 || ') ) Res' INTO lCount; IF (lCount = 0) RETURN TRUE; ELSE RETURN FALSE; END IF; End; $body$ language 'plpgsql'; Hi, thank for your help... I'm trying to execute your code but, when I run the the sentence, it throw a exception. For example, I run this line... select compare('select * from point limit 2', 'select * from point'); And, postgres throw the follow exceptio... ERROR: syntax error at or near EXCEPT LINE 1: ...COUNT(Res.*) FROM ( (select * from point limit 2 EXCEPT sel... ^ QUERY: SELECT COUNT(Res.*) FROM ( (select * from point limit 2 EXCEPT select * from point) UNION (select * from point EXCEPT select * from point limit 2) ) Res CONTEXT: PL/pgSQL function compare line 5 at EXECUTE statement ** Error ** ERROR: syntax error at or near EXCEPT Estado SQL:42601 Contexto:PL/pgSQL function compare line 5 at EXECUTE statement limit 2 does not work with EXCEPT. In the future reply to the list (Reply All) in order to keep the list in the conversation. Regards, Igor Neyman I want to know if there are other way to compare the result of two queries. Because the arguments will represent a query to execute and it can use everything sentence of SQL. If not there are other way, I wish know who are the limitations of EXCEPT. Greatens!! __ 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 compare the results of two queries?
El 17/09/13 12:56, Igor Neyman escribió: -Original Message- From: Juan Daniel Santana Rodés [mailto:jdsant...@estudiantes.uci.cu] Sent: Tuesday, September 17, 2013 12:51 PM To: Igor Neyman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to compare the results of two queries? I want to know if there are other way to compare the result of two queries. Because the arguments will represent a query to execute and it can use everything sentence of SQL. If not there are other way, I wish know who are the limitations of EXCEPT. Greatens!! __ In the modified function I put both queries in parenthesis, so this should allow pretty much anything in the query. Igor Neyman Thanks. I tested your code and worked fine. Now I only should catch the exception when the results of the querires has diferents munbers of columns. God bless you. __ 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
[GENERAL] ¿Cómo comparar el resultado de dos consultas?
Buenas... Estoy desarrollando una tarea en la cual necesito saber cómo comparar el resultado de dos consultas... He pensado en crear un procedimiento el cual reciba por parámetros ambas consultas respectivamente. Luego de alguna forma poder ejecutar las consultas y devolver si ambas tienen el mismo resultado. Como característica del problema, ambas consultas son de selección. Acá dejo un pedazo del código de lo que quiero hacer. create or replace function compare(sql1 character varying, sql2 character varying) returns boolean as $body$ Declare Begin --acá de alguna manera poder ejecutar ambas consultas y luego conpararla End; $body$ language 'plpgsql'; He estado estudiando y me he encontrado de que existe EXECUTE pero para usarlo, antes se debe haber usado PREPARE, y en este caso los valores de los parámetros ya son consultas confeccionadas. Por ejemplo la ejecución de la función sería algo como... select compare('select * from table1', 'select * from table2'); Para este caso el resultado sería falso, pues las consultas son ejecutadas sobre tablas distintas. Gracias de antemano. Saludos cordiales desde Cuba. __ 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