In a PostgreSQL WHERE clause, how do I find all rows whose ID's are NOT in an array?

Okay, I've got a stored procedure... how do I find all rows whose ID's are not in an array? (Keep in mind that I'm using a PostgreSQL array that is created dynamically when the stored procedure is created

Example:

|  people   |
-------------
| id | Name |
-------------
|  1 |开发者_Go百科 Bob  |
|  2 | Te   |
|  3 | Jack |
|  4 | John |

The array has somePGArray := [1,3], so a psuedo-query would look like this:

SELECT * FROM people WHERE id NOT IN (somePGArray)

Resulting query:

|  people   |
-------------
| id | Name |
-------------
|  2 | Te   |
|  4 | John |

As a bonus, I also have no idea how to create an array and append ID's to it, so if you have a quick hint how to do that, that'd be tremendously helpful. :-)

---

create table foo1 ( id integer, name text );
insert into foo1 values (1,'Bob'),(2,'Te'),(3,'Jack'),(4,'John');
select * from foo1 where id not in (1,2);
select * from foo1 where not (id = ANY(ARRAY[1,2]));

create or replace function so_example(int)
returns SETOF foo1 as $$
declare
  id alias for $1;
  idlist int[] := '{1}';
  q text;
  rec record;
begin
  idlist := idlist || ARRAY[id];
  q := 'select * from foo1 where not (id = ANY('||quote_literal(idlist)||'))';
  raise notice 'foo % %', idlist,q;
  for rec in execute(q) loop
    return next rec;
  end loop;
end; $$
language 'plpgsql';

select * from so_example(3);

---

If you're talking about an actual PostgreSQL array, use

SELECT * FROM people WHERE NOT id = ANY ('{1,3}')

---

Just remove the square braces:

WHERE id NOT IN (1,2)

As a bonus, your NOT IN clause could be populated with a subquery that would look like:

Where id not in (select id from sometable where some_field = somevalue)

You could also do a dynamic string concatenation to generate a comma-separated set and inject it into an ad hoc query.