Re: foreach statment? - Mailing list pgsql-general

From Tom Lane
Subject Re: foreach statment?
Date
Msg-id 24945.1053390804@sss.pgh.pa.us
Whole thread Raw
In response to foreach statment?  (Abdul-wahid Paterson <aw@lintrix.net>)
List pgsql-general
Abdul-wahid Paterson <aw@lintrix.net> writes:
> I would do something like this:

> select i.item_id from items i where (select count(item_id) from
> items_options where item_id=i.item_id) = 0;

> And then write a script that will go through the outputted list of
> item_id's and for each one do an insert statement like:

> insert into items_options values ($item_id, $n);

> Where $n is the number of my default option.

Use INSERT/SELECT:

insert into items_options
   select i.item_id, $n
   from items i where (select count(item_id) from
                       items_options where item_id=i.item_id) = 0;

BTW, I'd think about a NOT EXISTS instead of a COUNT test in the
WHERE ... should run faster ...

            regards, tom lane

pgsql-general by date:

Previous
From: "Jim C. Nasby"
Date:
Subject: Re: ERROR: Memory exhausted in AllocSetAlloc(188)
Next
From: Larry Rosenman
Date:
Subject: Re: pg_atoi problem with 7.3.2