Hello,
I have a Function in PL/pgSQL and I need to translate it to SQL92, but
I'm stuck.
###
CREATE OR REPLACE FUNCTION pruebas(varchar, varchar, integer)
RETURNS SETOF personal AS
$delimiter$
BEGIN
PERFORM id from documentos WHERE descripcion = $1;
IF NOT FOUND THEN INSERT INTO documentos(descripcion) VALUES($1);
END IF;
INSERT INTO personal(nombre,idtipodocumento,numdoc)
VALUES($2, (SELECT id from documentos WHERE descripcion = $1), $3);
END;
$delimiter$
LANGUAGE plpgsql;
###
Tables
CREATE TABLE documentos id serial NOT NULL, descripcion character varying(60), CONSTRAINT pkdocumentos PRIMARY KEY
(id)
CREATE TABLE personal id serial NOT NULL, nombre character varying(60), idtipodocumento smallint NOT NULL, numdoc
integer, CONSTRAINT pkpersonal PRIMARY KEY (id), CONSTRAINT fkpdoc FOREIGN KEY (idtipodocumento) REFERENCES
documentos(id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION, CONSTRAINT unqnumdoc UNIQUE (idtipodocumento,
numdoc)
Thanks and greetings for all
Marcelo