Question: I have these tables setup:  

NOMINATIONS: A table of award nominations  

NOMINATION\_NOMINEES: A table of users with a FK on NOMINATIONS.ID

One Nomination can be referenced by many nominees via the ID field.

```
SELECT a.*, COUNT(SELECT all records from NOMINATION_NOMINEES with this ID) AS "b" 
FROM NOMINATIONS a
LEFT JOIN NOMINATION_NOMINEES b on a.ID = b.ID

```

The results would look like:

> 
> ID | NOMINATION\_DESCRIPTION | ... | NUMBER\_NOMINEES
> 
> 
> 

Where NUMBER\_NOMINEES is the number of rows in the NOMINATION\_NOMINEES table with the current row's ID.

This is a tricky one, we are feeding this into a larger system so I'm hoping to get this in one query with a bunch of subqueries. Implementing subqueries into this has twisted my mind. Anyone have an idea of where to head with this?

I'm sure the above way is not close to a decent approach to this one, but I can't quite wrap my mind around this one.

Answer: 
Doing updates row-by-row in a loop is almost always a bad idea and **will** be extremely slow and won't scale. You should really find a way to avoid that.

After having said that: 

All your function is doing is to change the value of the column value in memory - you are just modifying the contents of a variable. If you want to update the data you need an `update` statement:

You need to use an `UPDATE` inside the loop:

```
CREATE OR REPLACE FUNCTION LoopThroughTable() 
  RETURNS VOID 
AS
$$
DECLARE 
   t_row the_table%rowtype;
BEGIN
    FOR t_row in SELECT * FROM the_table LOOP
        update the_table
            set resid = 1.0
        where pk_column = t_row.pk_column; --<<< !!! important !!!
    END LOOP;
END;
$$ 
LANGUAGE plpgsql;

```

Note that you *have* to add a `where` condition on the primary key to the `update` statement otherwise you would update **all** rows for **each** iteration of the loop.

A *slightly* more efficient solution is to use a cursor, and then do the update using `where current of`

```
CREATE OR REPLACE FUNCTION LoopThroughTable() 
  RETURNS VOID 
AS $$
DECLARE 
   t_curs cursor for 
      select * from the_table;
   t_row the_table%rowtype;
BEGIN
    FOR t_row in t_curs LOOP
        update the_table
            set resid = 1.0
        where current of t_curs;
    END LOOP;
END;
$$ 
LANGUAGE plpgsql;

```

---

> 
> So if I execute the UPDATE query after the loop has finished, will that commit the changes to the table? 
> 
> 
> 

No. The call to the function runs in the context of the calling transaction. So you need to `commit` after running `SELECT LoopThroughTable()` if you have disabled auto commit in your SQL client.

---

Note that the language name is an identifier, do not use single quotes around it. You should also avoid using keywords like `row` as variable names. 

Using [dollar quoting](http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING) (as I did) also makes writing the function body easier
You don't need a function for that.
All you need is to run this query:

```
UPDATE table SET resid = 1.0;

```

if you want to do it with a function you can use `SQL` function:

```
CREATE OR REPLACE FUNCTION LoopThroughTable()
  RETURNS VOID AS
$BODY$
UPDATE table SET resid = 1.0;
$BODY$
  LANGUAGE sql VOLATILE

```

if you want to use `plpgsql` then function would be:

```
CREATE OR REPLACE FUNCTION LoopThroughTable()
  RETURNS void AS
$BODY$
begin
       UPDATE table SET resid = 1.0;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE

```

Note that it is not recommended to use `plpgsql` functions for tasks that can be done with `Sql` functions.