CREATE OR REPLACE FUNCTION dodaj_podstrona_insert() returns opaque AS' begin for k in select id_sl_kolor from sl_kolor loop insert into kolor ( id_sl_kolor, id_podstrona, ilosc ) values ( k.id_sl_kolor, new.id_podstrona, 0 ); end loop; return new; end; ' LANGUAGE 'plpgsql';
a poźniej tak:
CREATE TRIGGER dodaj_podstrona before INSERT ON podstrona FOR each row execute procedure dodaj_podstrona_insert();
a moje tabele wygladaja tak:
CREATE TABLE "public"."podstrona" ( "id_podstrona" SERIAL, "id_domena" INTEGER NOT NULL, "nazwa" VARCHAR(255) NOT NULL, "sid1" INTEGER NOT NULL, "sid2" INTEGER NOT NULL, CONSTRAINT "pk_podstrona" PRIMARY KEY("id_podstrona"), CONSTRAINT "podstrona_sid1_key" UNIQUE("sid1"), CONSTRAINT "podstrona_sid2_key" UNIQUE("sid2"), CONSTRAINT "fk_podstrona_id_domena" FOREIGN KEY ("id_domena") REFERENCES "public"."domena"("id_domena") ) ;
CREATE TABLE "public"."sl_kolor" ( "id_sl_kolor" SERIAL, "kolor" VARCHAR(255) NOT NULL, CONSTRAINT "pk_sl_kolor" PRIMARY KEY("id_sl_kolor") );
CREATE TABLE "public"."kolor" ( "id_kolor" SERIAL, "id_sl_kolor" INTEGER NOT NULL, "id_podstrona" INTEGER NOT NULL, "ilosc" INTEGER DEFAULT 0 NOT NULL, CONSTRAINT "pk_kolor" PRIMARY KEY("id_kolor"), CONSTRAINT "fk_kolor_id_podstrona" FOREIGN KEY ("id_podstrona") REFERENCES "public"."podstrona"("id_podstrona"), CONSTRAINT "fk_kolor_id_sl_kolor" FOREIGN KEY ("id_sl_kolor") REFERENCES "public"."sl_kolor"("id_sl_kolor") );
komunikat ktory dostaje po próbie wykonania instrukcji INSERT do tabeli podstrona wygląda tak:
Kod
missing .. at end of SQL expression
no i co jest nie tak?