Translate Function PL/pgSQL to SQL92

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

Responses

Browse pgsql-sql by date

  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