From: | serviciotdf <serviciotdf(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Translate Function PL/pgSQL to SQL92 |
Date: | 2010-12-16 01:22:12 |
Message-ID: | 4D0969C4.5050205@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2010-12-16 07:38:11 | Re: Database consistency after a power shortage |
Previous Message | Alberto | 2010-12-15 15:12:38 | Database consistency after a power shortage |