<?xml version="1.0" encoding="utf-8"?>
<feed xmlns="http://www.w3.org/2005/Atom">
	<title type="html"><![CDATA[Forum PostgreSQL - Partycje na regułach i zwracana wartość po insert]]></title>
	<link rel="self" href="http://forum.postgresql.org.pl/extern.php?action=feed&amp;tid=1864&amp;type=atom"/>
	<updated>2013-07-05T10:36:07Z</updated>
	<generator>PunBB</generator>
	<id>https://forum.postgresql.org.pl/viewtopic.php?id=1864</id>
		<entry>
			<title type="html"><![CDATA[Odp: Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4635#p4635"/>
			<content type="html"><![CDATA[[quote=c_michal]Jedyne co mi przychodzi do głowy to zrezygnowanie w twojej funkcji z EXECUTE a wykorzystanie INSERT np

if table_name = 'tab_i1' then
  insert into tab_i1 values (dane.*) returning * into result;
elsif table_name = 'tab_i2' then
  insert into tab_i2 values (dane.*) returning * into result;
end if;

niestety to nadal dość kiepskie rozwiązanie

PS (nie mam teraz dostępu do serwera więc sprawdź sobie dokładnie składnie[/quote]


Ach, chyba się udało. Funkcja wygląda następująco :
CREATE OR REPLACE FUNCTION insert_into_p(table_name regclass, dane anyelement)
  RETURNS record AS
$BODY$
DECLARE
result magazyn_inout_pos_parent%rowtype;
BEGIN
	EXECUTE format('INSERT INTO %s SELECT ($1).* RETURNING *', table_name) USING dane INTO result;
	return result;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Na razie wygląda, że wszystko działa. Wg. reguł wpisuje dane tam gdzie trzeba i zwraca to co wpisał. 
Piękne dzięki za pomoc.]]></content>
			<author>
				<name><![CDATA[risc]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=1608</uri>
			</author>
			<updated>2013-07-05T10:36:07Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4635#p4635</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4634#p4634"/>
			<content type="html"><![CDATA[Jedyne co mi przychodzi do głowy to zrezygnowanie w twojej funkcji z EXECUTE a wykorzystanie INSERT np

if table_name = 'tab_i1' then
  insert into tab_i1 values (dane.*) returning * into result;
elsif table_name = 'tab_i2' then
  insert into tab_i2 values (dane.*) returning * into result;
end if;

niestety to nadal dość kiepskie rozwiązanie

PS (nie mam teraz dostępu do serwera więc sprawdź sobie dokładnie składnie]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2013-07-05T09:50:14Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4634#p4634</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4633#p4633"/>
			<content type="html"><![CDATA[[quote=c_michal]
użycie zmiennej record możesz prześledzić na poniższym przykładzie

select row (123,2,'aaa');

zwracana wartość to "(123,2,aaa)" zauważ, że nie ma tam cudzysłowów dla ciągu, twoja zmienna "dane" w funkcji insert_into_p to też record  tyle, że ma ustalone nazwy pól.
Niestety polecenie execute łączy twoją zmienną "dane" w następujący ciąg "INSERT INTO tab_i2 VALUES (123,2,aaa) RETURNING *;" (wywołanie select insert_into_p('tab_i2', row (123,2,'aaa'))), aby to działało trzeba by wywołać to tak
select insert_into_p('tab_i2', row (123,2,'''aaa''')) ale chyba oto Ci nie chodziło.[/quote]

No właśnie jak zapanować nad tymi cudzysłowami ? Zależy mi aby rekord był przekazywany przez NEW.* żeby nie modyfikować funkcji za każdym razem kiedy zmienie kolumny w tabeli. Po prostu te partycje mają działać transparentnie. Chcę używać insertów do partycjonowanych tabel i zwracać wartości jak do normalnych. Faktycznie w tej chwili funkcja tak miesza cudzysłowami że niektóre pola zagnieżdża w " a z innych tekstowych kompletnie usuwa '.]]></content>
			<author>
				<name><![CDATA[risc]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=1608</uri>
			</author>
			<updated>2013-07-05T08:15:35Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4633#p4633</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4632#p4632"/>
			<content type="html"><![CDATA[Nie zgodzę się z Tobą wywołanie [b]select * from tab_ins_f(1,'tab1 rek 2');[/b] spowoduje dodanie nowej wartości z nowym kluczem i zwrócenie dodanego rekordu, można więc za jej pomocą dodawać rekordy w wielu wątkach i za każdym razem uzyskać nową wartość klucza.

W moim przykładnie zostawiłem polecenie CREATE RULE tylko dlatego by zachować zgodność z twoim projektem (tak by nie trzeba było przerabiać całej aplikacji), tam gdzie masz potrzebę dodania nowej wartości do tabeli i odczytania klucza głównego musisz wywołać funkcję i to w w/w formacie, w przeciwnym wypadku zwracany typ to będzie record.

użycie zmiennej record możesz prześledzić na poniższym przykładzie

select row (123,2,'aaa');

zwracana wartość to "(123,2,aaa)" zauważ, że nie ma tam cudzysłowów dla ciągu, twoja zmienna "dane" w funkcji insert_into_p to też record  tyle, że ma ustalone nazwy pól.
Niestety polecenie execute łączy twoją zmienną "dane" w następujący ciąg "INSERT INTO tab_i2 VALUES (123,2,aaa) RETURNING *;" (wywołanie select insert_into_p('tab_i2', row (123,2,'aaa'))), aby to działało trzeba by wywołać to tak
select insert_into_p('tab_i2', row (123,2,'''aaa''')) ale chyba oto Ci nie chodziło.]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2013-07-04T21:52:56Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4632#p4632</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4631#p4631"/>
			<content type="html"><![CDATA[Niestety ten przykład nie jest bezpieczny (thread safe). Nie dostane z niego wartości np. kluczy zaraz po insercie a zanim wykonam select wiele może się zmienić. Postanowiłem go trochę zmodyfikować.
1. W regule zamiast instead insert dałem instead select insertfunc ze zwracaną wartością:
CREATE RULE "_INSERT_1" AS ON INSERT to tab WHERE (new.partycja=1) DO INSTEAD (SELECT insert_into_p('tab_i1', NEW.*) );
CREATE RULE "_INSERT_2" AS ON INSERT to tab WHERE (new.partycja=2) DO INSTEAD (SELECT insert_into_p('tab_i2', NEW.*) );
2. Funkcja insert wygląda następująco:
CREATE OR REPLACE FUNCTION insert_into_p(table_name regclass, dane tab)
  RETURNS RECORD AS
$BODY$
DECLARE
result tab;
BEGIN
	EXECUTE 'INSERT INTO '||table_name||' VALUES '|| dane ||' RETURNING *;' INTO result;
	return result;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Użycie:
insert into tab (partycja,nazwa) values (2,'tab2 rek 1');
Prawie działa - wpisuje dane tam gdzie trzeba niestety zawsze zwraca pusty rekord zamiast ten który zawierał wpisywane dane. W zasadzie zawsze zwraca puste wartości, również gdy zmodyfikuje funkcję tak by zwracała np tylko 1 pole jako ::text.
Dajcie znać jeśli macie jeszcze jakieś pomysły.]]></content>
			<author>
				<name><![CDATA[risc]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=1608</uri>
			</author>
			<updated>2013-07-04T18:06:50Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4631#p4631</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4630#p4630"/>
			<content type="html"><![CDATA[rzeczywiście postgres nie chce używać returning w momencie gdy są w RULE klauzule WHERE ale mam dla Ciebie taką propozycje. Napisz funkcję która za pomocą EXECUTE wykonuje odpowiedniego inserta i zwraca dane (będziesz mógł ją wykorzystywać tam gdzie aplikacja będzie tego potrzebowała) Poniżej uproszczony przykład takiego skryptu 

CREATE TABLE tab
(
  id serial primary key,
  partycja integer not null,
  nazwa varchar (50) not null
);

CREATE TABLE tab_i1
(
 CONSTRAINT tab_i1_pkey PRIMARY KEY (id),
 CONSTRAINT tab_i1_test check (partycja=1)
) inherits (tab);

CREATE TABLE tab_i2
(
 CONSTRAINT tab_i2_pkey PRIMARY KEY (id),
 CONSTRAINT tab_i2_test check (partycja=2)
) inherits (tab);

CREATE RULE "_INSERT_1" AS ON INSERT to tab WHERE (new.partycja=1) DO INSTEAD (insert into tab_i1 (partycja,nazwa) values (new.partycja,new.nazwa));
CREATE RULE "_INSERT_2" AS ON INSERT to tab WHERE (new.partycja=2) DO INSTEAD (insert into tab_i2 (partycja,nazwa) values (new.partycja,new.nazwa));

CREATE OR REPLACE FUNCTION tab_ins_f(partycja integer, nazwa character varying)
  RETURNS SETOF tab AS
$BODY$declare 
 r tab;
begin
 execute 'insert into tab_i'||partycja||' (partycja,nazwa) values ('||partycja||','''||nazwa||''') returning *' into r;
 return next r;
 return;
end;$BODY$
  LANGUAGE plpgsql VOLATILE;
[b]
przykład użycia
insert into tab (partycja,nazwa) values (2,'tab2 rek 1');
insert into tab (partycja,nazwa) values (1,'tab1 rek 1');
select * from tab_ins_f(1,'tab1 rek 2');
[/b]]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2013-07-04T08:33:17Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4630#p4630</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4629#p4629"/>
			<content type="html"><![CDATA[Wersja 9.2.4 w tej chwili na innym kompie 9.1 EnterpriseDB, też nie działa, z tego co wiem 8 też nie będzie działać.
Całe definicje załączone w 1 poście z tym że jeśli miało by to działać w ten sposób to linijka tworząca regułę w funkcji create_mag_pos powinna wyglądać tak :
EXECUTE 'CREATE RULE magazyn_inout_pos_' || mcid || '_insert_rule AS ON INSERT to magazyn_inout_pos_parent WHERE (mc_id=''' || mcid || ''' ) DO INSTEAD INSERT INTO ' || pname || ' VALUES (NEW.*) RETURNING *;';]]></content>
			<author>
				<name><![CDATA[risc]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=1608</uri>
			</author>
			<updated>2013-07-03T14:31:50Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4629#p4629</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4628#p4628"/>
			<content type="html"><![CDATA[którą masz wersje postgresa, i załącz skrypt, który Ci nie działa zarówno definicje jak i wywołanie]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2013-07-03T14:08:43Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4628#p4628</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4627#p4627"/>
			<content type="html"><![CDATA[Ach miał bym to zrobione pare dni temu bo to co piszesz ma ręce i nogi, niestety : BŁĄD:  listy RETURNING nie są obsługiwane w regułach warunkowych Stan SQL: 0A000]]></content>
			<author>
				<name><![CDATA[risc]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=1608</uri>
			</author>
			<updated>2013-07-03T14:04:51Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4627#p4627</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Odp: Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4626#p4626"/>
			<content type="html"><![CDATA[[quote=risc]
Niestety INSERTY tworzone w regułach pozbawione są możliwości zwracania wartości w posgresql-u.
[/quote]

O ile pamietam możesz wywołać insert z klauzulą returning np insert into tabela (id) values (1) returning *;
Jak tworzysz RULE to tez możesz ja tak napisać by obsługiwała returning.
np.
CREATE RULE _insert AS ON INSERT TO tabala DO INSTEAD(
  INSERT INTO a (id,name) VALUES (NEW.id, NEW.name) RETURNING id, name,
NULL::text;


Chyba nie do końca rozumiem twój problem. Napisz co dokładnie Ci nie działa?]]></content>
			<author>
				<name><![CDATA[c_michal]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=627</uri>
			</author>
			<updated>2013-07-03T13:28:12Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4626#p4626</id>
		</entry>
		<entry>
			<title type="html"><![CDATA[Partycje na regułach i zwracana wartość po insert]]></title>
			<link rel="alternate" href="https://forum.postgresql.org.pl/viewtopic.php?pid=4625#p4625"/>
			<content type="html"><![CDATA[Więc jest sobie tabela nadrzędna z pozycjami dokumentów np:
CREATE TABLE magazyn_inout_pos_parent
(
  doc_id character varying(25) NOT NULL, -- Referencja do dokumentu
  doc_pos integer NOT NULL,
  quantity numeric(12,2) NOT NULL,
  product_id integer NOT NULL,
  unit_price numeric(12,2) NOT NULL, -- cena za 1 unit
  mc_id character varying(12) NOT NULL, -- referencja do otwartego miesiąca
  CONSTRAINT magazyn_inout_pos_parent_pkey PRIMARY KEY (doc_id, doc_pos)
)
i tabela z miesiącami  :
CREATE TABLE miesiace_mag
(
  id character varying(12) NOT NULL,
  miesiac integer,
  rok integer,
  mag_id integer NOT NULL, -- FK do magazynu
  data date NOT NULL DEFAULT now(), -- Data która przez trigger zostanie podzielona na miesiąc i rok
  CONSTRAINT "PK_miesiace" PRIMARY KEY (id),
  CONSTRAINT "FK_miesiace_mag_id" FOREIGN KEY (mag_id)
      REFERENCES magazyny (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
Na której wyzwalany jest trigger tworzący partycje
CREATE TRIGGER utworz_partycje
  AFTER INSERT OR UPDATE OR DELETE
  ON miesiace_mag
  FOR EACH ROW
  EXECUTE PROCEDURE magmc_modyfikator();
Procedura magmc w zależności czy insert, update czy delete coś tam robi ale w każdym razie podczas insert wywołuje :

CREATE OR REPLACE FUNCTION create_mag_pos(mcid character varying, magnr integer)
  RETURNS boolean AS
$BODY$DECLARE 
	tname text:='magazyny.magazyn_inout_' || magnr;
	suffix text:='_doc';
	pname text:='magazyny.magazyn_inout_pos_' || mcid;
BEGIN
	EXECUTE 'CREATE TABLE ' || pname || ' (CHECK (mc_id=''' || mcid || ''')) INHERITS (magazyn_inout_pos_parent);';
	EXECUTE 'CREATE RULE magazyn_inout_pos_' || mcid || '_insert_rule AS ON INSERT to magazyn_inout_pos_parent WHERE (mc_id=''' || mcid || ''' ) DO INSTEAD INSERT INTO ' || pname || ' VALUES (NEW.*);';

	EXECUTE 'ALTER TABLE '||pname||' ADD CONSTRAINT "PK_'||pname||'" PRIMARY KEY ("doc_id", "doc_pos");';
	EXECUTE 'ALTER TABLE '|| pname ||' ADD CONSTRAINT "FK_'||pname||'_docid" FOREIGN KEY (doc_id) REFERENCES '||tname||' (id) ON UPDATE NO ACTION ON DELETE NO ACTION;';
	EXECUTE 'ALTER TABLE '|| pname ||' ADD CONSTRAINT "FK_'||pname||'_mcid" FOREIGN KEY (mc_id) REFERENCES miesiace_mag (id) ON UPDATE NO ACTION ON DELETE NO ACTION;';
	EXECUTE 'ALTER TABLE '|| pname ||' ADD CONSTRAINT "FK_'||pname||'_prodid" FOREIGN KEY (product_id) REFERENCES products (id) ON UPDATE NO ACTION ON DELETE NO ACTION;';

	RETURN TRUE;
END;

W tym wszystkim chodzi o to, żeby partycje i reguły były tworzone automatycznie podczas modyfikacji tabel i to działa. Problem pojawia się natomiast podczas wpisywania danych do tabeli kiedy potrzebuję zwrócić klucz łączony (doc_id, doc_pos);
Niestety INSERTY tworzone w regułach z clauzurą WHERE pozbawione są możliwości zwracania wartości w posgresql-u.
Klucz ten jest też tworzony wg. dość skomplikowanych funkcji i raczej nie interesuje mnie jego zamiana na sekwencje a następnie wybieranie tej wartości funkcjami curentval itp.
Czy istnieje jakiś elegancki sposób tworzenia partycji i pracy na zwracanych po insert wartościach.
Dajcie znać proszę jeśli macie na to jakiś sprytny sposób.]]></content>
			<author>
				<name><![CDATA[risc]]></name>
				<uri>https://forum.postgresql.org.pl/profile.php?id=1608</uri>
			</author>
			<updated>2013-07-03T09:49:58Z</updated>
			<id>https://forum.postgresql.org.pl/viewtopic.php?pid=4625#p4625</id>
		</entry>
</feed>
