选择 column = array 的行

以下是我正在努力做的事情的总结:

$array[0] = 1;
$array[1] = 2;


$sql = "SELECT * FROM table WHERE some_id = $array"

显然，有一些语法问题，但这是我想做的，我还没有找到任何东西，说明如何做到这一点。

目前，我的计划是这样做:

foreach($idList as $is)
$where .= 'some_id=' . $id . ' OR';
endforeach


$sql = "SELECT * FROM table WHERE " . $where;

So is there support in PostgreSQL to use an array to search, or do I have to do something similar to my solution?

168563 次浏览
小开
最佳答案 
SELECT  *
FROM    table
WHERE   some_id = ANY(ARRAY[1, 2])

或 ANSI兼容:

SELECT  *
FROM    table
WHERE   some_id IN (1, 2)

The ANY syntax is preferred because the array as a whole can be passed in a bound variable:

SELECT  *
FROM    table
WHERE   some_id = ANY(?::INT[])

You would need to pass a string representation of the array: {1,2}

小开 
   $array[0] = 1;
$array[2] = 2;
$arrayTxt = implode( ',', $array);
$sql = "SELECT * FROM table WHERE some_id in ($arrayTxt)"
小开

For dynamic SQL use:

'IN(' ||array_to_string(some_array, ',')||')'

Example

DO LANGUAGE PLPGSQL $$


DECLARE
some_array bigint[];
sql_statement text;


BEGIN


SELECT array[1, 2] INTO some_array;
RAISE NOTICE '%', some_array;


sql_statement := 'SELECT * FROM my_table WHERE my_column IN(' ||array_to_string(some_array, ',')||')';
RAISE NOTICE '%', sql_statement;


END;


$$;

结果: 注意: {1,2} 注意: SELECT * FROM my _ table WHERE my _ column IN (1,2)

小开

In my case, I needed to work with a column that has the data, so using IN() didn't work. 感谢@Quassnoi 提供的例子。 我的解决办法是:

SELECT column(s) FROM table WHERE expr|column = ANY(STRING_TO_ARRAY(column,',')::INT[])

我花了将近6个小时才偶然发现那根柱子。


提交答案