pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTranslate Function PL/pgSQL to SQL92
3+ messages / 2 participants
[nested] [flat]
* Translate Function PL/pgSQL to SQL92
@ 2010-12-16 01:22 serviciotdf <serviciotdf@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: serviciotdf @ 2010-12-16 01:22 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Translate Function PL/pgSQL to SQL92
@ 2010-12-16 11:17 Filip Rembiałkowski <filip.rembialkowski@gmail.com>
parent: serviciotdf <serviciotdf@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Filip Rembiałkowski @ 2010-12-16 11:17 UTC (permalink / raw)
To: serviciotdf <serviciotdf@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Translate Function PL/pgSQL to SQL92
@ 2010-12-17 15:26 serviciotdf <serviciotdf@gmail.com>
parent: Filip Rembiałkowski <filip.rembialkowski@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: serviciotdf @ 2010-12-17 15:26 UTC (permalink / raw)
To: pgsql-sql
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ó:
>
> 2010/12/16 serviciotdf <serviciotdf@gmail.com
> <mailto: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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-12-17 15:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-12-16 01:22 Translate Function PL/pgSQL to SQL92 serviciotdf <serviciotdf@gmail.com>
2010-12-16 11:17 ` Filip Rembiałkowski <filip.rembialkowski@gmail.com>
2010-12-17 15:26 ` serviciotdf <serviciotdf@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox