Re: Can triggers update other tables?

On Wed, Oct 19, 2011 at 7:24 PM, Rikard <rikard.bosnjakovic@gmail.com> wrote:
> VERSION = 'PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc
> (GCC) 3.4.6, 32-bit'
>
> I'm reading at http://www.postgresql.org/docs/8.4/static/sql-createtrigger.html
> and http://www.postgresql.org/docs/8.4/static/plpgsql.html but I
> cannot solve this problem. The very outline is that I'm having two
> tables (in reality there are a lot more), something like this:
>
>  SUMMARY ( id integer, num_stores integer; );
>  ITEMS ( id serial PRIMARY KEY; name text; some_reference integer,
> some_value smallint; ).
>
> When a row is inserted in ITEMS, I want to launch a trigger that
> issues a "UPDATE num_stores SET num_stores=(SELECT count(*) FROM items
> WHERE id = <trigger_table.id>) where id = <trigger_table.id>".
> Triggers seem to be run on functions returning plpgsql and I don't
> know how to issue ordinary SQL-commands in SQL, so I don't know how to
> reference these two tables in a single trigger.

Not following you here.  plpgsql functions are just ordinary SQL with
some extra features -- loops and such. you can do pretty much any ad
hoc sql you want inside a trigger...

CREATE OR REPLACE FUNCTION items_update() returns triggers AS
$$
BEGIN
UPDATE num_stores SET num_stores=(SELECT count(*) FROM items
 WHERE id = new.id) where id = new.id;
END;
$$ language plpgsql;

note these kind of triggers are usually harder to write than they look
and my have unpleasant performance characteristics.  for
materialization/aggregation, it might be a good idea to look at
statement level triggers and detect updated rows based on a timestamp.

merlin