Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id 7FB64476532
	for <pgsql-sql@postgresql.org>; Mon, 27 May 2002 19:54:18 -0400 (EDT)
Received: from joeconway.com (unknown [63.210.180.150])
	by postgresql.org (Postfix) with ESMTP id A2919475873
	for <pgsql-sql@postgresql.org>; Mon, 27 May 2002 19:18:00 -0400 (EDT)
Received: from [192.168.5.2] (account jconway HELO joeconway.com)
	by joeconway.com (CommuniGate Pro SMTP 3.5.6)
	with ESMTP-TLS id 471477; Mon, 27 May 2002 16:17:16 -0700
Message-ID: <3CF2BEAE.7020207@joeconway.com>
Date: Mon, 27 May 2002 16:18:06 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:0.9.9) Gecko/20020311
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Sandeep Chibber <sandeep@vreach.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem with the result set of postgres
References: <000f01c2054f$4d79ac10$1302a8c2@multicast.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200205/287
X-Sequence-Number: 7922

Sandeep Chibber wrote:
 > in this table there are two records and the output is also coming
 > twice There is no change in the number 168269272 ,168269272
 >
 > Please guide me how to get the result set through a java bean
 >
 > *My Requirement :*
 >
 > 1.I want to retrieve multiple records from the table using function.
 > The function call will come from the java bean.
 >
 > 2. The table will have different datatypes.
 >

Support for returning setof composite is limited in 7.2.x and before.
This works on 7.2:

test=# select version();
                           version
-----------------------------------------------------------
  PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

test=# create table newtest (name varchar(50), address varchar(50));

insert into newtest(name,address) values('a','b');
insert into newtest(name,address) values('c','d');

CREATE FUNCTION newtestfunc () RETURNS setof newtest AS
'SELECT name,address FROM newtest'
LANGUAGE 'SQL';

select name(newtestfunc()), address(newtestfunc());CREATE
test=#
test=# insert into newtest(name,address) values('a','b');
INSERT 41947 1
test=# insert into newtest(name,address) values('c','d');
INSERT 41948 1
test=#
test=# CREATE FUNCTION newtestfunc () RETURNS setof newtest AS
test-# 'SELECT name,address FROM newtest'
test-# LANGUAGE 'SQL';
CREATE
test=#
test=# select name(newtestfunc()), address(newtestfunc());
  name | address
------+---------
  a    | b
  c    | d
(2 rows)

Note that the function returns "setof newtest", not "setof varchar" 
because varchar is a base, scalar type. You want to return a composite 
type, which means you need a table defined which reflects the return 
tuple number and type of attributes. The numbers that you were getting 
are pointers to the result tuples, but you can get at the individual 
fields as shown above.

HTH,

Joe