Re: How to Return Table From Function

Hello

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;
> 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

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

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

Regards

Pavel Stehule