Thread: The tuple structure of a not-yet-assigned record is indeterminate.

Hi there, i'm new to postgres. I want to create view when adding new row. So what i've got:

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 * FROM t_matches.someint;
RETURN NULL;
END;
$$ language plpgsql;

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


Whats wrong, I supposed that id is not reserverd at the moment, so what can I do?

And here is some infos about table

league=# \d t_leagues
                                  Table "public.t_leagues"
   Column   |         Type          |                       Modifiers                       
------------+-----------------------+--------------------------------------------------------
 id         | integer               | not null default nextval('t_leagues_id_seq'::regclass)
 name       | character varying(20) | not null
 data_start | date                  |
 data_end   | date                  |
 awans      | smallint              | not null default 0
 baraz      | smallint              | not null default 0
 spadek     | smallint              | not null default 0
Indexes:
    "t_leagues_pkey" PRIMARY KEY, btree (id)
Triggers:
    league AFTER INSERT ON t_leagues FOR EACH STATEMENT EXECUTE PROCEDURE add_view()

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.

> 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.

--
Craig Ringer

M L wrote:

>   CREATE VIEW tabelka AS SELECT someint FROM t_matches;

What exactly are you trying to do here? If it worked how you've written
it,  you'd get the value of `someint' repeated once for each row that
appears in t_matches.

I don't know exactly why you're seeing the behaviour you are. However,
the it works if you build the statement you want as a string and invoke
it using EXECUTE:

CREATE OR REPLACE FUNCTION add_view() RETURNS trigger AS $$
DECLARE
  someint integer;
BEGIN
  EXECUTE 'CREATE VIEW tabelka AS SELECT '||NEW.id||' FROM t_matches;';
RETURN NULL;
END;
$$ language plpgsql;

... though the view produced isn't very useful.

--
Craig Ringer

thx4help, it just proof of concept. Real view is:

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 * FROM tabela(someint);
RETURN NULL;
END;
$$ language plpgsql;

Also I have function and new type:

CREATE TYPE tables AS (name varchar(20), games smallint, wins smallint, draws smallint, losts smallint, goals smallint, connected smallint, points smallint);

CREATE OR REPLACE FUNCTION tabela(int) RETURNS SETOF tables AS
$BODY$
DECLARE
    r tables%rowtype;
    i integer;
    teams record;
BEGIN
    FOR teams IN SELECT * FROM t_teams WHERE league_id=$1
    LOOP  
        -- can do some processing here
        --RAISE NOTICE 'wartosc teams.id %', teams.id;
        SELECT teams.full_name, games(teams.id), wins(teams.id), draws(teams.id), losts(teams.id),goals(teams.id) ,connected(teams.id) ,points(teams.id) FROM t_teams WHERE league_id=$1 INTO r;
        --RAISE NOTICE 'czy mamy jakies inne r %', r;
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE ’plpgsql’ ;
pg_field_name(resource result, int field_number);

And when I make query i get:
league=# INSERT INTO t_leagues (name) VALUES('3liga');
NOTICE:  dodajesz nowa lige 45
NOTICE:  dodajesz nowa lige 45
ERROR:  there is no parameter $1
CONTEXT:  SQL statement "CREATE VIEW tabelka AS SELECT * FROM tabela( $1 )"
PL/pgSQL function "add_view" line 7 at SQL statement

General purpose of this trigger is to create new table view whenever new league is added. I think that problem is with "    FOR teams IN SELECT * FROM t_teams WHERE league_id=$1" from function tabela(). Any ideas how to cope with that? How should I create that kind of view?