SCRIPT SQL

SCRIPT SQL

CREATE TABLE almacenes_05 (
nroalm bigserial NOT NULL,
desalm character varying(20)
);


CREATE TABLE auditoria_05 (
nrodoc bigserial NOT NULL,
operacioncharacter varying(1),
"user" character varying(10),
fecha timestamp with time zone,
cantidad integer,
despro character varying(20),
desalm character varying(20)
);


CREATETABLE consolidado_05 (
codpro character varying(10) NOT NULL,
total integer
);


CREATE TABLE movimientos_05 (
nroalm integer,
codpro character varying(10),
cantidad integer);


CREATE TABLE productos_05 (
codpro character varying(10) NOT NULL,
despro character varying(20)
);


INSERT INTO almacenes_05 VALUES (1, '1er Almacen');
INSERT INTO almacenes_05VALUES (2, '2do Almacen');
INSERT INTO almacenes_05 VALUES (3, '3er Almacen');
INSERT INTO almacenes_05 VALUES (4, '4to Almacen');


INSERT INTO productos_05 VALUES ('CEP1', 'Cepillo');
INSERTINTO productos_05 VALUES ('ESC1', 'Escoba');
INSERT INTO productos_05 VALUES ('BRO1', 'Brocha');
INSERT INTO productos_05 VALUES ('RAS1', 'Rastrillo');
INSERT INTO productos_05 VALUES ('PAL1','Pala');

INSERT INTO consolidado_05 VALUES ('CEP1', 0);
INSERT INTO consolidado_05 VALUES ('ESC1', 0);
INSERT INTO consolidado_05 VALUES ('RAS1', 0);
INSERT INTO consolidado_05 VALUES ('BRO1', 0);ALTER TABLE ONLY almacenes_05
ADD CONSTRAINT almacenes_05_pkey PRIMARY KEY (nroalm);


ALTER TABLE ONLY auditoria_05
ADD CONSTRAINT auditoria_05_pkey PRIMARY KEY (nrodoc);


ALTERTABLE ONLY consolidado_05
ADD CONSTRAINT consolidado_05_pkey PRIMARY KEY (codpro);


ALTER TABLE ONLY productos_05
ADD CONSTRAINT productos_05_pkey PRIMARY KEY (codpro);

--CREATEEXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

CREATE FUNCTION consolidar_datos() RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE proxdoc integer := (SELECT MAX(nrodoc)+1 FROM...