trv sql - Page 630 - Developer IT

Optimize "not in" query

- by Vasu

Please help optimize this query: SELECT ts.SiteId, COUNT(ts.SiteId) AS Count FROM ts WHERE ts.SiteId not in (SELECT ts.SiteId FROM ts WHERE ts.uniqueid = 'xxx') GROUP BY ts.SiteId ORDER BY Count DESC

What are the rules governing how a bind variable can be used in Postgres and where is this defined?

I can have a table and function defined as: CREATE TABLE mytable ( mycol integer ); INSERT INTO mytable VALUES (1); CREATE OR REPLACE FUNCTION myfunction (l_myvar integer) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol = l_myvar; RETURN l_myrow; END; $$ LANGUAGE plpgsql; In this case l_myvar acts as a bind variable for the value passed when I call: SELECT * FROM myfunction(1); and returns the row where mycol = 1 If I redefine the function as: CREATE OR REPLACE FUNCTION myfunction (l_myvar integer) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol IN (l_myvar); RETURN l_myrow; END; $$ LANGUAGE plpgsql; SELECT * FROM myfunction(1); still returns the row where mycol = 1 However, if I now change the function definition to allow me to pass an integer array and try to this array in the IN clause, I get an error: CREATE OR REPLACE FUNCTION myfunction (l_myvar integer[]) RETURNS mytable AS $$ DECLARE l_myrow mytable; BEGIN SELECT * INTO l_myrow FROM mytable WHERE mycol IN (array_to_string(l_myvar, ',')); RETURN l_myrow; END; $$ LANGUAGE plpgsql; Analysis reveals that although: SELECT array_to_string(ARRAY[1, 2], ','); returns 1,2 as expected SELECT * FROM myfunction(ARRAY[1, 2]); returns the error operator does not exist: integer = text at the line: WHERE mycol IN (array_to_string(l_myvar, ',')); If I execute: SELECT * FROM mytable WHERE mycol IN (1,2); I get the expected result. Given that array_to_string(l_myvar, ',') evaluates to 1,2 as shown, why arent these statements equivalent. From the error message it is something to do with datatypes, but doesnt the IN(variable) construct appear to be behaving differently from the = variable construct? What are the rules here? I know that I could build a statement to EXECUTE, treating everything as a string, to achieve what I want to do, so I am not looking for that as a solution. I do want to understand though what is going on in this example. Is there a modification to this approach to make it work, the particular example being to pass in an array of values to build a dynamic IN clause without resorting to EXECUTE? Thanks in advance Craig

Search Results

Search found 27337 results on 1094 pages for 'trv sql'.

Page 630/1094 | < Previous Page | 626 627 628 629 630 631 632 633 634 635 636 637 | Next Page >

- by Vasu

- by Craig Miles

- by Sennheiser

- by Xelluloid

- by Raúl Roa

- by Bruno Martinez

- by Peter Kaleta

- by goober

- by John

- by Furqan Muhammad Khan

- by mrNepal

- by fishhead

- by Jean-Philippe

- by Gabriel

- by William

- by user1311030

- by hermann

- by jungle_programmer

- by Shantanu Gupta

- by warren

- by jjj

- by smokey20

- by Frank Developer

- by Sorin Comanescu

- by HAJJAJ

< Previous Page | 626 627 628 629 630 631 632 633 634 635 636 637 | Next Page >