2009/3/23 Craig Ringer
<craig@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.