pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

How to Return Table From Function
3+ messages / 3 participants
[nested] [flat]

* How to Return Table From Function
@ 2012-01-22 13:09 Rehan Saleem <pk_rehan@yahoo.com>
  2012-01-22 13:53 ` Re: How to Return Table From Function Pavel Stehule <pavel.stehule@gmail.com>
  2012-01-22 21:42 ` Re: How to Return Table From Function Rob Sargentg <robjsargent@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Rehan Saleem @ 2012-01-22 13:09 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How to Return Table From Function
  2012-01-22 13:09 How to Return Table From Function Rehan Saleem <pk_rehan@yahoo.com>
@ 2012-01-22 13:53 ` Pavel Stehule <pavel.stehule@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Pavel Stehule @ 2012-01-22 13:53 UTC (permalink / raw)
  To: Rehan Saleem <pk_rehan@yahoo.com>; +Cc: pgsql-sql

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= 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-RE...


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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How to Return Table From Function
  2012-01-22 13:09 How to Return Table From Function Rehan Saleem <pk_rehan@yahoo.com>
@ 2012-01-22 21:42 ` Rob Sargentg <robjsargent@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Rob Sargentg @ 2012-01-22 21:42 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2012-01-22 21:42 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-22 13:09 How to Return Table From Function Rehan Saleem <pk_rehan@yahoo.com>
2012-01-22 13:53 ` Pavel Stehule <pavel.stehule@gmail.com>
2012-01-22 21:42 ` Rob Sargentg <robjsargent@gmail.com>

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