ako synchronizovat databazove skupiny podla tabulky?

[Honza Pazdziora]

On Tue, Feb 24, 2004 at 07:29:25AM +0100, Jan Serak wrote:
> >
> >pouzivam postgresql verzie 7.4.1.
> 
> Postgresql neznam, odpoved bude obecna.

Praveze ne. To, ze Oracle po DDL operaci dela commit neznamena, ze
to je obecny princip.

> >ktory by po pridani zaznamu v tabulke skupina vytvoril databazovu skupinu
> >CREATE OR REPLACE FUNCTION skupinaNovyF () RETURNS TRIGGER AS '
> >    BEGIN
> >      CREATE GROUP NEW.nazov;
> >      RETURN NEW;
> >    END;
> >  ' LANGUAGE 'plpgsql';
> >
> >toto mi samozrejme nefunguje, lebo NEW.nazov je retazec znakov a do CREATE 
> >GROUP xyz sa nedava retazec.

Je treba ten prikaz vytvorit a spustit pomoci

	execute 'create group ' || new.nazov;

> Podle meho nazoru to nebude fungovat nikdy. Snazite se totiz uvnitr DML 
> (Data Manipulation Language) pouzit prikaz DDL (Data Definition 
> Language), i kdyz se muze zdat, ze skupina nedefinuje datovou strukturu.
> 
> Prikazy DDL jsou VZDY samostatnymi transakcemi. Jak by se mel Postgresql 
> (ale i cokoli jineho) podle Vas zachovat, kdybyste hypoteticky dosahl 
> sveho zameru a pak mu predlozil:
> 
> 	INSERT INTO vase_tabulka ... (pritom trigger zalozi skupinu)
> 	ROLLBACK;

> select * from skupina ;
id,nazov
[0 rows of 2 fields returned]
> select * from jezek1 ;
DBD::Pg::st execute failed: ERROR:  Relation "jezek1" does not exist.
> /rollback
> select * from jezek2 ;
DBD::Pg::st execute failed: ERROR:  Relation "jezek2" does not exist.
> /rollback
> insert into skupina values (1, 'jezek1') ;
[1 row affected]
> select * from jezek1 ;
id
[0 rows of 1 fields returned]
> insert into skupina values (1, 'jezek2') ;
[1 row affected]
> select * from jezek2 ;
id
[0 rows of 1 fields returned]
> select * from skupina ;
id,nazov
'1','jezek1                          '
'1','jezek2                          '
[2 rows of 2 fields returned]
> /rollback
> select * from jezek2 ;
DBD::Pg::st execute failed: ERROR:  Relation "jezek2" does not exist.

Tedy je videt, ze Postgres ma DDL operace ve standardnim transakcnim
modu.

> select * from skupina ;
id,nazov
[0 rows of 2 fields returned]
> drop table skupina ;
[unknown number of rows affected]
> select * from skupina ;
DBD::Pg::st execute failed: ERROR:  Relation "skupina" does not exist.
> /rollback
> select * from skupina ;
id,nazov
[0 rows of 2 fields returned]

-- 
------------------------------------------------------------------------
 Honza Pazdziora | adelton na fi.muni.cz | http://www.fi.muni.cz/~adelton/
 .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ...
		Only self-confident people can be simple.