Обсуждение: Translate Function PL/pgSQL to SQL92
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
2010/12/16 serviciotdf <serviciotdf@gmail.com>
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
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
Perfect!
The query worked fine!
Answer:
###
CREATE OR REPLACE FUNCTION pruebas(varchar, varchar, integer)
RETURNS void
AS
$delimiter$
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;
$delimiter$
LANGUAGE SQL;
###
Thanks Filip!
Marcelo
El 16/12/10 08:17, Filip Rembiałkowski escribió:
The query worked fine!
Answer:
###
CREATE OR REPLACE FUNCTION pruebas(varchar, varchar, integer)
RETURNS void
AS
$delimiter$
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;
$delimiter$
LANGUAGE SQL;
###
Thanks Filip!
Marcelo
El 16/12/10 08:17, Filip Rembiałkowski escribió:
2010/12/16 serviciotdf <serviciotdf@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