> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Igor Neyman > Sent: Tuesday, September 17, 2013 12:02 PM > To: Juan Daniel Santana Rodés; pgsql-general@xxxxxxxxxxxxxx > Subject: Re: How to compare the results of two queries? > > > > > -----Original Message----- > > From: Juan Daniel Santana Rodés [mailto:jdsantana@xxxxxxxxxxxxxxxxxx] > > Sent: Tuesday, September 17, 2013 11:54 AM > > To: Igor Neyman > > Subject: Re: 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 > Well, if you really want to use "limit" clause in your queries, the following should work (even with the "limit"): 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'; Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
