X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 667638B9E42; Wed, 2 Feb 2005 22:45:37 +0000 (GMT) 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 49868-05; Wed, 2 Feb 2005 22:45:30 +0000 (GMT) Received: from pd6mo4no.prod.shaw.ca (idcmail-mo2no.cg.shawcable.net [64.59.134.9]) by svr1.postgresql.org (Postfix) with ESMTP id E8EB18B9B48; Wed, 2 Feb 2005 22:45:32 +0000 (GMT) Received: from pd8mr7no.prod.shaw.ca (pd8mr7no-qfe2.prod.shaw.ca [10.0.144.228]) by l-daemon (Sun ONE Messaging Server 6.0 HotFix 1.01 (built Mar 15 2004)) with ESMTP id <0IBB000TZ37RNI40@l-daemon>; Wed, 02 Feb 2005 15:45:27 -0700 (MST) Received: from pn7ml4no.prod.shaw.ca ([10.0.149.113]) by pd8mr7no.prod.shaw.ca (Sun ONE Messaging Server 6.0 HotFix 1.01 (built Mar 15 2004)) with ESMTP id <0IBB00AIK37RLQP0@pd8mr7no.prod.shaw.ca>; Wed, 02 Feb 2005 15:45:27 -0700 (MST) Received: from Dell4500 (S01060004236fc1f7.wp.shawcable.net [24.77.13.13]) by l-daemon (iPlanet Messaging Server 5.2 HotFix 1.18 (built Jul 28 2003)) with SMTP id <0IBB0014T37K4B@l-daemon>; Wed, 02 Feb 2005 15:45:27 -0700 (MST) Date: Wed, 02 Feb 2005 16:45:42 -0600 From: George Weaver Subject: Re: PL/PgSQL - returning multiple columns ... To: "Marc G. Fournier" , pgsql-sql@postgresql.org Message-id: <00c701c50978$f01c9e20$6400a8c0@Dell4500> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Mailer: Microsoft Outlook Express 6.00.2900.2180 Content-type: text/plain; reply-type=response; charset=iso-8859-1; format=flowed Content-transfer-encoding: 7bit X-Priority: 3 X-MSMail-priority: Normal References: <20050202170643.R27579@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.568 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, FORGED_RCVD_HELO X-Spam-Level: * X-Archive-Number: 200502/50 X-Sequence-Number: 20161 Hi Marc, One option is to create a simple data type and return the rowtype of the datatype eg CREATE TYPE tserverload AS ("server_name" text, "load_avg" int4); CREATE FUNCTION getserverload() RETURNS tserverload AS 'DECLARE r tserverload%rowtype; etc. You would then return r, comprised of r.server_name and r.load_avg. George ----- Original Message ----- From: "Marc G. Fournier" To: Sent: Wednesday, February 02, 2005 3:10 PM Subject: [SQL] PL/PgSQL - returning multiple columns ... > > I have a function that I want to return 'server_name, avg(load_avg)' ... > if I wanted to return matching rows in a table, I can do a 'setof > ', with a for loop inside ... but what do I set the 'RETURNS' to if > I want to return the results of query that returns only two fields of a > table, or, in the case of the above, one column and oen 'group by' column? > > thanks ... > > > ---- > Marc G. Fournier Hub.Org Networking Services > (http://www.hub.org) > Email: scrappy@hub.org Yahoo!: yscrappy ICQ: > 7615664 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >