Hola lista Sobre este tema no hay comentarios? Lo estoy haciendo mal o interpretando erróneamente? Escucho al menos sus críticas
El 10 ago. 2017 2:49 PM, "Hellmuth Vargas" <hiv...@gmail.com> escribió: > > > Un caso adicional > > test=# begin transaction; > BEGIN > test=# insert into padre(id,texto) > test-# select a.dato,chr(dato) from generate_series(33,255,1) as a(dato); > INSERT 0 223 > test=# with elimina as ( > test(# delete from padre where id%2=0 returning id,texto > test(# )select * from padre; -- trae todos > id | texto > -----+-------- > 33 | ! > 34 | " > 35 | # > 36 | $ > 37 | % > 38 | & > 39 | ' > 40 | ( > 41 | ) > 42 | * > 43 | + > 44 | , > 45 | - > 46 | . > 47 | / > 48 | 0 > 49 | 1 > 50 | 2 > 51 | 3 > 52 | 4 > 53 | 5 > 54 | 6 > 55 | 7 > 56 | 8 > > > test=# select * from padre; -- trae solo los impares > id | texto > -----+-------- > 33 | ! > 35 | # > 37 | % > 39 | ' > 41 | ) > 43 | + > 45 | - > 47 | / > 49 | 1 > 51 | 3 > 53 | 5 > 55 | 7 > 57 | 9 > 59 | ; > 61 | = > 63 | ? > 65 | A > 67 | C > 69 | E > 71 | G > 73 | I > 75 | K > 77 | M > 79 | O > test=# rollback; > ROLLBACK > test=# > > 2017-08-10 14:33 GMT-05:00 Hellmuth Vargas <hiv...@gmail.com>: > >> >> Hola Lista >> >> Estaba revisando porque unas sentencias se ejecutaban y otras no con WITH >> y prepare este script para que ustedes me ayuden a entender: >> >> test=# create table padre(id int primary key, texto text); >> CREATE TABLE >> test=# create table hijo(id int primary key, texto text, padre_id int >> references padre(id)); >> CREATE TABLE >> test=# begin transaction; >> BEGIN >> test=# with base as( >> test(# insert into padre(id,texto) >> test(# select a.dato,chr(dato) from generate_series(33,255,1) as a(dato) >> returning id,texto >> test(# ),hijos as ( >> test(# insert into hijo(id, texto, padre_id) >> test(# select -a.id,'el char de ' || a.id || 'es: ' || texto,a.id from >> base as a >> test(# ) >> test-# select * from >> test-# padre as a >> test-# join hijo as b on a.id=b.padre_id; >> id | texto | id | texto | padre_id >> ----+-------+----+-------+---------- >> (0 rows) >> >> no sale nada pero si en la misma transacción ejecuto nuevamente el ultimo >> select: >> >> >> test=# select * from >> test-# padre as a >> test-# join hijo as b on a.id=b.padre_id; >> id | texto | id | texto | padre_id >> -----+--------+------+--------------------------+---------- >> 33 | ! | -33 | el char de 33es: ! | 33 >> 34 | " | -34 | el char de 34es: " | 34 >> 35 | # | -35 | el char de 35es: # | 35 >> 36 | $ | -36 | el char de 36es: $ | 36 >> 37 | % | -37 | el char de 37es: % | 37 >> 38 | & | -38 | el char de 38es: & | 38 >> 39 | ' | -39 | el char de 39es: ' | 39 >> 40 | ( | -40 | el char de 40es: ( | 40 >> 41 | ) | -41 | el char de 41es: ) | 41 >> 42 | * | -42 | el char de 42es: * | 42 >> 43 | + | -43 | el char de 43es: + | 43 >> 44 | , | -44 | el char de 44es: , | 44 >> 45 | - | -45 | el char de 45es: - | 45 >> 46 | . | -46 | el char de 46es: . | 46 >> 47 | / | -47 | el char de 47es: / | 47 >> 48 | 0 | -48 | el char de 48es: 0 | 48 >> 49 | 1 | -49 | el char de 49es: 1 | 49 >> 50 | 2 | -50 | el char de 50es: 2 | 50 >> 51 | 3 | -51 | el char de 51es: 3 | 51 >> 52 | 4 | -52 | el char de 52es: 4 | 52 >> 53 | 5 | -53 | el char de 53es: 5 | 53 >> 54 | 6 | -54 | el char de 54es: 6 | 54 >> 55 | 7 | -55 | el char de 55es: 7 | 55 >> 56 | 8 | -56 | el char de 56es: 8 | 56 >> test=# rollback; >> ROLLBACK >> >> ------------------------------------------------------------ >> ----------------------- >> ------------------------------------------------------------ >> ----------------------- >> >> test=# begin transaction; >> BEGIN >> test=# with base as( >> test(# insert into padre(id,texto) >> test(# select a.dato,chr(dato) from generate_series(33,255,1) as a(dato) >> returning id,texto >> test(# ),hijos as ( >> test(# insert into hijo(id, texto, padre_id) >> test(# select -a.id,'el char de ' || a.id || 'es: ' || texto,a.id from >> padre as a ---no inserta >> test(# ) >> test-# select * from >> test-# padre as a >> test-# join hijo as b on a.id=b.padre_id; >> id | texto | id | texto | padre_id >> ----+-------+----+-------+---------- >> (0 rows) >> >> No inserto en el hijo. >> >> test=# select * from >> test-# padre as a >> test-# join hijo as b on a.id=b.padre_id; >> id | texto | id | texto | padre_id >> ----+-------+----+-------+---------- >> (0 rows) >> >> test=# select * from padre; >> id | texto >> -----+-------- >> 33 | ! >> 34 | " >> 35 | # >> 36 | $ >> 37 | % >> 38 | & >> 39 | ' >> 40 | ( >> 41 | ) >> 42 | * >> 43 | + >> 44 | , >> 45 | - >> 46 | . >> 47 | / >> 48 | 0 >> 49 | 1 >> 50 | 2 >> 51 | 3 >> 52 | 4 >> 53 | 5 >> 54 | 6 >> 55 | 7 >> 56 | 8 >> test=# select * from hijo; >> id | texto | padre_id >> ----+-------+---------- >> (0 rows) >> >> test=# rollback; >> ROLLBACK >> >> ------------------------------------------------------------ >> ----------------------- >> ------------------------------------------------------------ >> ----------------------- >> >> test=# begin transaction; >> BEGIN >> test=# insert into padre(id,texto) >> test-# select a.dato,chr(dato) from generate_series(33,255,1) as a(dato); >> INSERT 0 223 >> test=# >> test=# with elimina as ( >> test(# delete from padre where id%2=0 returning id,texto >> test(# ),hijos as ( >> test(# insert into hijo(id, texto, padre_id) >> test(# select -a.id,'el char de ' || a.id || 'es: ' || a.texto,a.id from >> padre as a returning id, texto, padre_id >> test(# ) >> test-# select * from >> test-# hijos as b; >> ERROR: insert or update on table "hijo" violates foreign key constraint >> "hijo_padre_id_fkey" >> DETAIL: Key (padre_id)=(34) is not present in table "padre". >> >> >> ok aunque en hijos emplee la tabla padre. >> >> >> test=# >> test=# rollback; >> ROLLBACK >> >> ------------------------------------------------------------ >> ----------------------- >> ------------------------------------------------------------ >> ----------------------- >> >> test=# begin transaction; >> BEGIN >> test=# with base as( >> test(# insert into padre(id,texto) >> test(# select a.dato,chr(dato) from generate_series(33,255,1) as a(dato) >> returning id,texto >> test(# ),hijos as ( >> test(# insert into hijo(id, texto, padre_id) >> test(# select -a.id,'el char de ' || a.id || 'es: ' || texto,a.id from >> padre as a ---no inserta >> test(# ) >> test-# select * from >> test-# padre as a >> test-# join hijo as b on a.id=b.padre_id; >> id | texto | id | texto | padre_id >> ----+-------+----+-------+---------- >> (0 rows) >> >> aquí no ve los registros de la tabla padre que se habían insertado en >> base..... >> >> >> test=# select * from hijo; >> id | texto | padre_id >> ----+-------+---------- >> (0 rows) >> >> test=# select * from padre; >> id | texto >> -----+-------- >> 33 | ! >> 34 | " >> 35 | # >> 36 | $ >> 37 | % >> 38 | & >> 39 | ' >> 40 | ( >> 41 | ) >> 42 | * >> 43 | + >> 44 | , >> 45 | - >> 46 | . >> 47 | / >> 48 | 0 >> 49 | 1 >> 50 | 2 >> 51 | 3 >> 52 | 4 >> 53 | 5 >> 54 | 6 >> 55 | 7 >> 56 | 8 >> test=# >> >> test=# rollback; >> ROLLBACK >> -- >> Cordialmente, >> >> Ing. Hellmuth I. Vargas S. >> Esp. Telemática y Negocios por Internet >> >> > > > -- > Cordialmente, > > Ing. Hellmuth I. Vargas S. > Esp. Telemática y Negocios por Internet > Oracle Database 10g Administrator Certified Associate > EnterpriseDB Certified PostgreSQL 9.3 Associate > >
