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