2009/3/23 Craig Ringer <cr...@postnewspapers.com.au>
> M L wrote:
>
> > CREATE TRIGGER league AFTER insert ON t_leagues FOR STATEMENT EXECUTE
> > PROCEDURE add_view();
> >
> > Then in psql I made an query and got error:
> >
> > league=# INSERT INTO t_leagues (name) VALUES('3liga');
> > ERROR: record "new" is not assigned yet
> > DETAIL: The tuple structure of a not-yet-assigned record is
> indeterminate.
> > CONTEXT: PL/pgSQL function "add_view" line 4 at RAISE
>
> `NEW' and `OLD' refer to the tuple operated on by this call of the
> trigger. They're not valid for FOR EACH STATEMENT triggers, since the
> statement might've added/modified/deleted zero or more than one tuple.
>
> If you want to see the values of the tuples modified, use a FOR EACH ROW
> trigger.
>
I was trying varius trigers when I use:
league=# CREATE OR REPLACE FUNCTION add_view() RETURNS trigger AS $$
DECLARE
someint integer;
BEGIN
RAISE NOTICE 'dodajesz nowa lige %', NEW.id;
someint := NEW.id;
RAISE NOTICE 'dodajesz nowa lige %', someint;
CREATE VIEW tabelka AS SELECT someint FROM t_matches;
RETURN NULL;
END;
$$ language plpgsql;
CREATE TRIGGER league AFTER insert ON t_leagues FOR EACH ROW EXECUTE
PROCEDURE add_view();
I've got:
league=# INSERT INTO t_leagues (name) VALUES('3liga');
NOTICE: dodajesz nowa lige 31
NOTICE: dodajesz nowa lige 31
ERROR: there is no parameter $1
CONTEXT: SQL statement "CREATE VIEW tabelka AS SELECT $1 FROM t_matches"
PL/pgSQL function "add_view" line 7 at SQL statement
Any ideas?
>
> > Whats wrong, I supposed that id is not reserverd at the moment
>
> That's not the case. Your trigger is being called *AFTER* the row is
> inserted, so the ID must've been assigned. In any case, default
> expressions (including those used to assign values from sequences) are
> actually evaluated even before the BEFORE triggers are invoked.
>
That was my first thought, that it should be already assigned.
