This is a summary of what I am trying to do:
$array[0] = 1;
$array[1] = 2;
$sql = "SELECT * FROM table WHERE some_id = $array"
Obviously, there are some syntax issues, but this is what I want to do, and I haven't found anything yet that shows how to do it.
Currently, my plan is to do something along these lines:
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?
This question is related to
postgresql
$array[0] = 1;
$array[2] = 2;
$arrayTxt = implode( ',', $array);
$sql = "SELECT * FROM table WHERE some_id in ($arrayTxt)"
In my case, I needed to work with a column that has the data, so using IN() didn't work. Thanks to @Quassnoi for his examples. Here is my solution:
SELECT column(s) FROM table WHERE expr|column = ANY(STRING_TO_ARRAY(column,',')::INT[])
I spent almost 6 hours before I stumble on the post.
For dynamic SQL use:
'IN(' ||array_to_string(some_array, ',')||')'
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;
$$;
Result:
NOTICE: {1,2}
NOTICE: SELECT * FROM my_table WHERE my_column IN(1,2)
Source: Stackoverflow.com