2010/12/16 serviciotdf <servicio...@gmail.com> > 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) > > > If I understand correctly, you mean translating this function into a sequence of plain SQL commands:
INSERT INTO documentos(descripcion) SELECT $1 WHERE NOT EXISTS ( SELECT id FROM documentos WHERE descripcion = $1 ); INSERT INTO personal ( nombre, idtipodocumento, numdoc ) SELECT $2, ( SELECT id FROM documentos WHERE descripcion = $1 ), $3; of course you will need to bind / pass parameters... HTH Filip
