From pk_rehan@yahoo.com Sun Jan  22 13:10:11 2012
Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id A4793AF1395
	for <pgsql-sql@postgresql.org>; Sun, 22 Jan 2012 09:10:11 -0400 (AST)
Received: from nm28-vm0.bullet.mail.ne1.yahoo.com ([98.138.91.22])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <pk_rehan@yahoo.com>) id 1RoxBQ-0000U1-ST
	for pgsql-sql@postgresql.org; Sun, 22 Jan 2012 13:10:10 +0000
Received: from [98.138.90.55] by nm28.bullet.mail.ne1.yahoo.com with NNFMP;
	22 Jan 2012 13:09:55 -0000
Received: from [98.138.87.6] by tm8.bullet.mail.ne1.yahoo.com with NNFMP;
	22 Jan 2012 13:09:55 -0000
Received: from [127.0.0.1] by omp1006.mail.ne1.yahoo.com with NNFMP;
	22 Jan 2012 13:09:55 -0000
X-Yahoo-Newman-Property: ymail-3
X-Yahoo-Newman-Id: 545597.72089.bm@omp1006.mail.ne1.yahoo.com
Received: (qmail 23601 invoked by uid 60001); 22 Jan 2012 13:09:55 -0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024;
	t=1327237795; bh=6/n86zfmxyQyaru9XdkcAGbiJt6hpRiP3mBXTSazYKk=;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type;
	b=kyE+zvCjvykpsnDIQ5+FF+2ZRxEtEQKXFFWKQSX4PyZqXyTRxJzqY/Yhw00Fh9Z3DubpkPeYhfGuOayaRc29Ze5P8FfLBgI5VhSOaXHL1c4nJ8RZaOpJzxDVSxgQcCtuZOOc69/FZRB7lQgu7LqJZ86yyncMbLoQ6t421bzElz0=
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type;
	b=gw0f/n2LN5OOoJlaVORpsw58ddr02RbhPdEoOyKguZqXlv8RcoDb7U9U+twCvE2bYolYJhaxh7ga4zUv3tbEACcB0mVuOvlrTY2mug5EVbLCJIA9k9QgoDGpZcPq93g5FyPijZpC7CKrp8k97ccxc6fxhykM6GfB5eij74YeNiw=;
X-YMail-OSG: YroIFK0VM1n8VVcQ7Jg8teOQcGmX_wuqTsJFyMI3xbA61Y2
	Wv8oKIu6YGmEaHEJd._5mPqTjJbWKX_Zt40f6RpdHKKi1Z7.YInN.YZcuVwU
	IANs5674pQvD46q0qP2.xiXKB34hqdrTIRECubbtcEGg9CDcylEGuMUWat8W
	iIJ.D61QFYlfWKSm5yCuM1cLCp8HcUjReJNfpKBAi1Y8Bz1BU861qsI_9yMv
	7wgPjekIsHuQFgTWyDZfYMGVXm3kQWEc0xApzS14clJ1MgStz.K7Jt_Iy3Si
	HPU5RZjFOhR0QjnquLtL1QrAcJyKKp6fbSJdAKOtMEVy.evLlDtpHlrt.ekm
	2l4yDgK8U1VM_UlDjTFmBA.fJnLxeoI6GDMkpJxZiuQHfg3m8AfP_pj.bRpP
	ilEYe.tdenlDFlSzd2GlleCrllSsyoLrPLyuY4BtmeWd9eGw6h2djT7rpiVI -
Received: from [119.152.104.10] by web121606.mail.ne1.yahoo.com via HTTP;
	Sun, 22 Jan 2012 05:09:55 PST
X-Mailer: YahooMailWebService/0.8.116.331537
Message-ID: <1327237795.19340.YahooMailNeo@web121606.mail.ne1.yahoo.com>
Date: Sun, 22 Jan 2012 05:09:55 -0800 (PST)
From: Rehan Saleem <pk_rehan@yahoo.com>
Reply-To: Rehan Saleem <pk_rehan@yahoo.com>
Subject: How to Return Table From Function
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="1458340343-1199520699-1327237795=:19340"
X-Pg-Spam-Score: -0.6 (/)
X-Archive-Number: 201201/98
X-Sequence-Number: 36269

--1458340343-1199520699-1327237795=:19340
Content-Type: text/plain; charset=us-ascii

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

--1458340343-1199520699-1327237795=:19340
Content-Type: text/html; charset=us-ascii

<html><body><div style="color:#000; background-color:#fff; 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></body></html>
--1458340343-1199520699-1327237795=:19340--



From pavel.stehule@gmail.com Sun Jan  22 13:54:44 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id AC004AF1395
	for <pgsql-sql@postgresql.org>; Sun, 22 Jan 2012 09:54:44 -0400 (AST)
Received: from mail-qw0-f53.google.com ([209.85.216.53])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <pavel.stehule@gmail.com>) id 1RoxsV-0007Nk-W0
	for pgsql-sql@postgresql.org; Sun, 22 Jan 2012 13:54:43 +0000
Received: by qabg24 with SMTP id g24so1307518qab.19
	for <pgsql-sql@postgresql.org>; Sun, 22 Jan 2012 05:54:27 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	bh=YDqpSZBpE7E/4S3R6IRXCd70VeY52KGqktTMWLnWU+U=;
	b=VO9pEmRSgSo6jm1x2uF0dDQbc7JLUx85k2fIwp/OrtXqvFSm86xr2+yadNlePdpFsG
	Nq9JfleRzM/Jh9Yv93X/rG4cf33PoWWsdc60DmhQRBPs2w1SJjleei3DOqsd+55Qditj
	Bis1/KR9al44EW5aEE1FW/80QsRjCFdDoymC4=
Received: by 10.224.216.74 with SMTP id hh10mr5172933qab.45.1327240467191;
	Sun, 22 Jan 2012 05:54:27 -0800 (PST)
MIME-Version: 1.0
Received: by 10.229.191.71 with HTTP; Sun, 22 Jan 2012 05:53:46 -0800 (PST)
In-Reply-To: <1327237795.19340.YahooMailNeo@web121606.mail.ne1.yahoo.com>
References: <1327237795.19340.YahooMailNeo@web121606.mail.ne1.yahoo.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sun, 22 Jan 2012 14:53:46 +0100
Message-ID: 
 <CAFj8pRD1+D08No9-0TL1xdeWYHAhkaOVyK7c+agDJz6iUgX9ZA@mail.gmail.com>
Subject: Re: How to Return Table From Function
To: Rehan Saleem <pk_rehan@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201201/99
X-Sequence-Number: 36270

Hello

2012/1/22 Rehan Saleem <pk_rehan@yahoo.com>:
> 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=3D user_id and bloodgroup>=3Dbg and
> (centredistance<=3Dcenter_distance or center_distance=3D1)
> 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

http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.h=
tml#PLPGSQL-STATEMENTS-RETURNING


postgres=3D# create table t2(a int, b int);
CREATE TABLE
postgres=3D# insert into t2 values(10,20),(30,40);
INSERT 0 2
postgres=3D# create or replace function rt2() returns table(a int, b int)
postgres-# as $$
postgres$# begin
postgres$#   return query select t2.a, t2.b from t2;
postgres$#   return;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=3D# select * from rt2();
 a  =E2=94=82 b
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=BC=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80
 10 =E2=94=82 20
 30 =E2=94=82 40
(2 rows)

Regards

Pavel Stehule



From robjsargent@gmail.com Sun Jan  22 21:43:12 2012
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--