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

[M L <novemberox@xxxxxxxxx>]

2009/3/23 Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx>

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?