From: Rob Sargentg <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: How to Return Table From Function
Date: Sun, 22 Jan 2012 14:42:51 -0700
Message-ID: <4F1C82DB.8090207@gmail.com> (raw)
In-Reply-To: <1327237795.19340.YahooMailNeo@web121606.mail.ne1.yahoo.com>
References: <1327237795.19340.YahooMailNeo@web121606.mail.ne1.yahoo.com>
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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com
Subject: Re: How to Return Table From Function
In-Reply-To: <4F1C82DB.8090207@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox