Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 532CD13364DE for ; Fri, 17 Dec 2010 11:26:45 -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 29829-03 for ; Fri, 17 Dec 2010 15:26:38 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-vw0-f46.google.com (mail-vw0-f46.google.com [209.85.212.46]) by mail.postgresql.org (Postfix) with ESMTP id 70A4D133616B for ; Fri, 17 Dec 2010 11:26:37 -0400 (AST) Received: by vws16 with SMTP id 16so210269vws.19 for ; Fri, 17 Dec 2010 07:26:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:subject:references:in-reply-to :content-type; bh=iqldO+qSYl3bxtMTj1iTRQz3X0/IwCZS8cRPR1WFZqM=; b=I9oEqik6A95NRsM5vi+CWn3u91USLNGoiiQRCUYfrs86LxD4U3vBD152kaY8+R1bK5 4aQpc2m+TvZizPwx1onfW8sYOq+YEeTAYDKhOCwFGtgEQovJOhE/6Txa1VkduRAqS+Xg VcOVyK+Pnl9zkO6Tg6zRxLsT9D7Y2WDR+p/Xg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type; b=p/Z9/wU4hRt+bTUIwfC+JkWHFcH6pmO6FvHNRSh6HRmUC+9/DjthQQ/cC+g+C6qSeM SGfU7zrHKXo7jUrYTkhr9igJU58nv+LJ2iAwzfUEGixyoz7ingYFum62iiUU4688zrjq XySHIhcDKGSeZJfIc8zn40DmbpLs/DME6xW1M= Received: by 10.220.81.78 with SMTP id w14mr256828vck.120.1292599597288; Fri, 17 Dec 2010 07:26:37 -0800 (PST) Received: from [192.168.1.10] ([190.178.64.97]) by mx.google.com with ESMTPS id u6sm135727vby.7.2010.12.17.07.26.34 (version=SSLv3 cipher=RC4-MD5); Fri, 17 Dec 2010 07:26:35 -0800 (PST) Message-ID: <4D0B8126.1040300@gmail.com> Date: Fri, 17 Dec 2010 12:26:30 -0300 From: serviciotdf User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Translate Function PL/pgSQL to SQL92 References: <4D0969C4.5050205@gmail.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------080405030407050506090102" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.588 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201012/86 X-Sequence-Number: 34922 This is a multi-part message in MIME format. --------------080405030407050506090102 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > > > 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 > --------------080405030407050506090102 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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>
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

--------------080405030407050506090102--