Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 0FF349CF00F
	for <pgsql-sql@postgresql.org>; Sun, 22 Jan 2012 17:43:12 -0400 (AST)
Received: from mail-iy0-f174.google.com ([209.85.210.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <robjsargent@gmail.com>) id 1Rp5Bw-0005qK-Iq
	for pgsql-sql@postgresql.org; Sun, 22 Jan 2012 21:43:13 +0000
Received: by iacb35 with SMTP id b35so2721411iac.19
	for <pgsql-sql@postgresql.org>; Sun, 22 Jan 2012 13:43:00 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type;
	bh=q1awa6a0JYNg7sZqzq1g+dYkBbiRYROIXy+3a/GCSlA=;
	b=ZvT79JLWMIhhS8XsssI755DwARa6emvE7CjfyOU8bFL2Z92N9Lz656cc6kjRlFqM7W
	0zVkmMCtKP94zMLhGVqBnZGM7pzvv/3rx7rt8q4Ct3qLYDJNE+A3Kzgi0MiBkV9CBZ+E
	gBTeu87J6AC7mvNdPflls9EbON+Xp+tahXzvk=
Received: by 10.50.41.202 with SMTP id h10mr4027611igl.23.1327268580168;
	Sun, 22 Jan 2012 13:43:00 -0800 (PST)
Received: from [192.168.1.109] (rsargent.dsl.xmission.com. [199.104.125.126])
	by mx.google.com with ESMTPS id
	g34sm38994634ibk.10.2012.01.22.13.42.57 (version=SSLv3 cipher=OTHER);
	Sun, 22 Jan 2012 13:42:59 -0800 (PST)
Message-ID: <4F1C82DB.8090207@gmail.com>
Date: Sun, 22 Jan 2012 14:42:51 -0700
From: Rob Sargentg <robjsargent@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.24) Gecko/20111108 Lightning/1.0b2 Thunderbird/3.1.16
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: How to Return Table From Function
References: <1327237795.19340.YahooMailNeo@web121606.mail.ne1.yahoo.com>
In-Reply-To: <1327237795.19340.YahooMailNeo@web121606.mail.ne1.yahoo.com>
Content-Type: multipart/alternative;
	boundary="------------070706060409040309000507"
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201201/100
X-Sequence-Number: 36271

This is a multi-part message in MIME format.
--------------070706060409040309000507
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 01/22/2012 06:09 AM, Rehan Saleem wrote:
> hi , i have created this function
>
> CREATE OR REPLACE FUNCTION totaloverlapcount(user_id integer , bg 
> integer ,center_distance integer)
> RETURNS varchar AS $$
>
> DECLARE percentage record;
> BEGIN
>
>
>
> select fname, lname, count(userid) totalcount 
> ,100.00*count(useriddetails)/totaluser into percentage
> from users
> where userid= user_id and bloodgroup>=bg and
> (centredistance<=center_distance or center_distance=1)
> group by fname, lname, user_id;
> return percentage;
>
>
> its just a dummy function , but all i want to know that how can i 
> return fname , lname totalcount and percentage from this function in 
> the form of table , not the return type varchar.
> thanks

-Sorry cannot get to a server at the moment to check this sql, but if 
you want to address the field of the result table you need to create the 
type for the returned table.

create type reportTypeName(fname text, lname text, totalcount integer);
CREATE OR REPLACE FUNCTION totaloverlapcount(user_id integer , bg 
integer ,center_distance integer)
RETURNS setof reportTypeName AS $$


Then you can do
  select lname, totalcount as tally from totaloverlapcount( id, bg, cdist);



--------------070706060409040309000507
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    On 01/22/2012 06:09 AM, Rehan Saleem wrote:
    <blockquote
      cite="mid:1327237795.19340.YahooMailNeo@web121606.mail.ne1.yahoo.com"
      type="cite">
      <div style="color: rgb(0, 0, 0); background-color: rgb(255, 255,
        255); font-family: times new roman,new york,times,serif;
        font-size: 12pt;">
        <div>hi , i have created this function <br>
        </div>
        <div><br>
        </div>
        <div>CREATE OR REPLACE FUNCTION totaloverlapcount(user_id
          integer , bg integer ,center_distance integer)<br>
          RETURNS varchar AS $$<br>
          <br>
          DECLARE percentage record;<br>
          BEGIN<br>
          <br>
          <br>
          <br>
          select fname, lname, count(userid) totalcount
          ,100.00*count(useriddetails)/totaluser into percentage<br>
          from users<br>
          where userid= user_id and bloodgroup&gt;=bg and<br>
          (centredistance&lt;=center_distance or center_distance=1)<br>
          group by fname, lname, user_id;<br>
          return percentage;</div>
        <div><br>
        </div>
        <div><br>
        </div>
        <div>its just a dummy function , but all i want to know that how
          can i return fname , lname totalcount and percentage from this
          function in the form of table , not the return type varchar.</div>
        <div>thanks<br>
        </div>
      </div>
    </blockquote>
    <br>
    -Sorry cannot get to a server at the moment to check this sql, but
    if you want to address the field of the result table you need to
    create the type for the returned table.<br>
    <br>
    create type reportTypeName(fname text, lname text, totalcount
    integer);<br>
    CREATE OR REPLACE FUNCTION totaloverlapcount(user_id integer , bg
    integer ,center_distance integer)<br>
    RETURNS setof reportTypeName AS $$<br>
    <br>
    <br>
    Then you can do<br>
    &nbsp;select lname, totalcount as tally from totaloverlapcount( id, bg,
    cdist);<br>
    <br>
    <br>
  </body>
</html>

--------------070706060409040309000507--