Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 61D981337B98 for ; Thu, 16 Dec 2010 07:17:16 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 52702-05 for ; Thu, 16 Dec 2010 11:17:09 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ey0-f178.google.com (mail-ey0-f178.google.com [209.85.215.178]) by mail.postgresql.org (Postfix) with ESMTP id 41F641337B53 for ; Thu, 16 Dec 2010 07:17:08 -0400 (AST) Received: by eyh5 with SMTP id 5so1866831eyh.23 for ; Thu, 16 Dec 2010 03:17:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:cc:content-type; bh=x+wTxDEtNwtZcfx8yXL3n7f2nfz/BBxMoue3C9WhJss=; b=B861bAAz/g64USey6e5dmpVv2EnBqRKotlzs6DQ63YTc4tuXK87iX0wMlqVsYJeRBy gS5rgPvogL/Q4JIATpnXe5Yv14a5nJnXiUIUzet0TEN4EVYwHLdPdjTfxT/SfGt2R3LF AhvftqmewuontsD+bJb/a93BPuHsU235AaDm0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=rrGfaAu1jEcpQpMke0HeS1AGSkZlbr1jDYGtFT5g2luRYBT0u+UkYFz6DH1uFSZNYt rWygUYmsD2gfWu+Q1wjG9j+NkCFi7v91pYyxawvFH12FDbAWRbtrdqHGyqtzBDtGC1tE fGNStQORtkoGimv9tlRxmVNNKth7EvCTDwPg0= MIME-Version: 1.0 Received: by 10.213.7.17 with SMTP id b17mr1348072ebb.44.1292498227462; Thu, 16 Dec 2010 03:17:07 -0800 (PST) Received: by 10.213.32.6 with HTTP; Thu, 16 Dec 2010 03:17:07 -0800 (PST) In-Reply-To: <4D0969C4.5050205@gmail.com> References: <4D0969C4.5050205@gmail.com> Date: Thu, 16 Dec 2010 12:17:07 +0100 Message-ID: Subject: Re: Translate Function PL/pgSQL to SQL92 From: =?UTF-8?Q?Filip_Rembia=C5=82kowski?= To: serviciotdf Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0015174c1a20b881830497853101 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201012/80 X-Sequence-Number: 34916 --0015174c1a20b881830497853101 Content-Type: text/plain; charset=UTF-8 2010/12/16 serviciotdf > 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 --0015174c1a20b881830497853101 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
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= 9;m stuck.

###
CREATE OR REPLACE FUNCTION pruebas(varchar, varchar, integer)
RETURNS SETOF personal AS
$delimiter$
BEGIN
PERFORM id from documentos WHERE descripcion =3D $1;
IF NOT FOUND THEN
=C2=A0 =C2=A0INSERT INTO documentos(descripcion) VALUES($1);
END IF;
INSERT INTO personal(nombre,idtipodocumento,numdoc)
VALUES($2, (SELECT id from documentos WHERE descripcion =3D $1), $3);
END;
$delimiter$
LANGUAGE plpgsql;
###

Tables

CREATE TABLE documentos
=C2=A0id serial NOT NULL,
=C2=A0descripcion character varying(60),
=C2=A0CONSTRAINT pkdocumentos PRIMARY KEY (id)


CREATE TABLE personal
=C2=A0id serial NOT NULL,
=C2=A0nombre character varying(60),
=C2=A0idtipodocumento smallint NOT NULL,
=C2=A0numdoc integer,
=C2=A0CONSTRAINT pkpersonal PRIMARY KEY (id),
=C2=A0CONSTRAINT fkpdoc FOREIGN KEY (idtipodocumento)
=C2=A0 =C2=A0 =C2=A0REFERENCES documentos (id) MATCH SIMPLE
=C2=A0 =C2=A0 =C2=A0ON UPDATE CASCADE ON DELETE NO ACTION,
=C2=A0CONSTRAINT unqnumdoc UNIQUE (idtipodocumento, numdoc)



If I understand correctly, you=C2=A0mean translat= ing this function into a sequence of plain SQL commands:

INSERT INTO= documentos(descripcion) SELECT $1
WHERE NOT EXISTS ( SELECT id FROM doc= umentos WHERE descripcion =3D $1 );

INSERT INTO personal ( nombre, idtipodocumento, numdoc )
SELECT $2, = ( SELECT id FROM documentos WHERE descripcion =3D $1 ), $3;

of cours= e you will need to bind / pass=C2=A0parameters...

HTH

Filip
--0015174c1a20b881830497853101--