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: CAFj8pRD1+D08No9-0TL1xdeWYHAhkaOVyK7c+agDJz6iUgX9ZA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

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-2014 The PostgreSQL Global Development Group