2009/3/23 Craig Ringer <cr...@postnewspapers.com.au>
> 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?
