Skip site navigation (1) Skip section navigation (2)

Re: How to Return Table From Function

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Rehan Saleem <pk_rehan(at)yahoo(dot)com>
Cc: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: How to Return Table From Function
Date: 2012-01-22 13:53:46
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql

2012/1/22 Rehan Saleem <pk_rehan(at)yahoo(dot)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;
> 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

postgres=# create table t2(a int, b int);
postgres=# insert into t2 values(10,20),(30,40);
postgres=# 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;
postgres=# select * from rt2();
 a  │ b
 10 │ 20
 30 │ 40
(2 rows)


Pavel Stehule

In response to

pgsql-sql by date

Next:From: Rob SargentgDate: 2012-01-22 21:42:51
Subject: Re: How to Return Table From Function
Previous:From: Rehan SaleemDate: 2012-01-22 13:09:55
Subject: How to Return Table From Function

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group