From flavio@gral.org.br Wed Nov 16 20:26:14 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AA52CD89E5 for ; Wed, 16 Nov 2005 16:26:14 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40622-02 for ; Wed, 16 Nov 2005 20:26:14 +0000 (GMT) X-Greylist: delayed 00:06:39.061188 by SQLgrey- Received: from smtp.locasite.com.br (smtp2-gw.fasthost.com.br [200.183.77.52]) by svr1.postgresql.org (Postfix) with SMTP id 2E7CAD7257 for ; Wed, 16 Nov 2005 16:26:10 -0400 (AST) Received: (qmail 25709 invoked by uid 0); 16 Nov 2005 20:19:30 -0000 Received: from flavio@gral.org.br by smtp2.fasthost.com.br; 16 Nov 2005 20:19:30 -0000 Received: from 17924477.rjo.virtua.com.br (HELO ?192.168.1.2?) (200.179.244.77) by smtp.fasthost.com.br with SMTP; 16 Nov 2005 20:19:29 -0000 Subject: Receive a record not a tuple - plpgsql From: =?ISO-8859-1?Q?Fl=E1vio?= Brito To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary="=-xL5yvlBj7cQSJo77Tbsg" Date: Wed, 16 Nov 2005 18:21:31 -0200 Message-Id: <1132172491.26344.13.camel@localhost> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4-3mdk X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001, UPPERCASE_25_50=0] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200511/218 X-Sequence-Number: 19591 --=-xL5yvlBj7cQSJo77Tbsg Content-Type: text/plain Content-Transfer-Encoding: 7bit Dear Friends How can I call a function into a function? My problem is: I'm trying to calculate a tax(inss) over a employer salary. I created a function called inss that do it correctly, but when I create another one to show more attributes (inss is not a attribute, it is calculate over a salary) I receive a record (like {1,Mary,32.45} not a tuple. How can I solve it? CREATE TABLE emp ( cod numeric name text NOT NULL, last_date timestamp, last_user text, salary numeric, CONSTRAINT pkey PRIMARY KEY (cod) ) CREATE FUNCTION inss() RETURNS numeric AS ' SELECT salary*0.11 FROM emp; 'LANGUAGE 'sql'; --------------------------------------------------------------------------------------------- It is OK --------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION mostra_inss() RETURNS setof record AS ' select name, inss(), salary from emp; 'LANGUAGE 'sql'; --------------------------------------------------------------------------------------------- --- Only for test --- DROP FUNCTION show_inss(); --- Only for test --- select inss(); --------------------------------------------------------------------------------------------- --- Another Test - Not OK CREATE OR REPLACE FUNCTION show_inss() RETURNS VARCHAR AS ' DECLARE inss float; BEGIN SELECT INTO inss inss() FROM emp; RETURN inss; END; ' LANGUAGE 'plpgsql'; --------------------------------------------------------------------------------------------- --- Another Test (NOT OK) --------------------------------------------------------------------------------------------- create or replace function test_inss() returns record as ' DECLARE d record; BEGIN for d in select name,salary,inss() from emp loop end loop; RETURN; END; ' language 'plpgsql'; select test_inss(); --=-xL5yvlBj7cQSJo77Tbsg Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit Dear Friends

How can I call a function into a function? My problem is: I'm trying to calculate a tax(inss) over a employer salary. I created a function called inss that do it correctly, but when I create another one to show more attributes (inss is not a attribute, it is calculate over a salary) I receive a record (like {1,Mary,32.45} not a tuple. How can I solve it?

CREATE TABLE emp
(
  cod numeric
  name text NOT NULL,
  last_date timestamp,
  last_user text,
  salary numeric,
  CONSTRAINT pkey PRIMARY KEY (cod)
)

CREATE FUNCTION inss() RETURNS numeric AS '
SELECT salary*0.11 FROM emp;
'LANGUAGE 'sql';
---------------------------------------------------------------------------------------------
It is OK
---------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mostra_inss() RETURNS setof record AS '

select name, inss(), salary from emp;

'LANGUAGE 'sql';
---------------------------------------------------------------------------------------------
--- Only for test --- DROP FUNCTION show_inss();
--- Only for test --- select inss();
---------------------------------------------------------------------------------------------
--- Another Test - Not OK

CREATE OR REPLACE FUNCTION show_inss() RETURNS VARCHAR AS '
DECLARE
inss float;
BEGIN
SELECT INTO inss inss() FROM emp;
RETURN inss;
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------------------------------
--- Another Test (NOT OK)
---------------------------------------------------------------------------------------------
create or replace function test_inss() returns record as '
      DECLARE
         d record;
      BEGIN
         for d in select name,salary,inss() from emp
         loop     
         end loop;
      RETURN;
   END;
   ' language 'plpgsql';

select test_inss(); --=-xL5yvlBj7cQSJo77Tbsg-- From tfo@sitening.com Mon Nov 21 04:42:57 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EC5E7D8D18 for ; Mon, 21 Nov 2005 00:42:57 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40734-08 for ; Mon, 21 Nov 2005 04:42:55 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by svr1.postgresql.org (Postfix) with ESMTP id 35348D8C85 for ; Mon, 21 Nov 2005 00:42:55 -0400 (AST) Received: from chilco.textdrive.com (chilco.textdrive.com [207.7.108.242]) by svr2.postgresql.org (Postfix) with ESMTP id 8D144F0B6D for ; Mon, 21 Nov 2005 04:42:54 +0000 (GMT) Received: from [192.168.15.104] (pcp0012204803pcs.blairblvd.tn.nash.comcast.net [69.245.49.69]) by chilco.textdrive.com (Postfix) with ESMTP id 11069DA95C; Mon, 21 Nov 2005 04:42:54 +0000 (UTC) In-Reply-To: <1132172491.26344.13.camel@localhost> References: <1132172491.26344.13.camel@localhost> Mime-Version: 1.0 (Apple Message framework v746.2) Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed Message-Id: <7D3B24F0-256E-4DC7-A32B-75ABEE53D83F@sitening.com> Cc: pgsql-admin@postgresql.org Content-Transfer-Encoding: quoted-printable From: "Thomas F. O'Connell" Subject: Re: Receive a record not a tuple - plpgsql Date: Sun, 20 Nov 2005 22:42:42 -0600 To: =?ISO-8859-1?Q?Fl=E1vio_Brito?= X-Mailer: Apple Mail (2.746.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/268 X-Sequence-Number: 19641 On Nov 16, 2005, at 2:21 PM, Fl=E1vio Brito wrote: > How can I call a function into a function? My problem is: I'm =20 > trying to calculate a tax(inss) over a employer salary. I created a =20= > function called inss that do it correctly, but when I create =20 > another one to show more attributes (inss is not a attribute, it is =20= > calculate over a salary) I receive a record (like {1,Mary,32.45} =20 > not a tuple. How can I solve it? Have you looked into set-returning functions? It seems like that =20 might be what you're looking for: http://www.postgresql.org/docs/8.1/static/plpgsql-control-=20 structures.html#PLPGSQL-STATEMENTS-RETURNING -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax)=