Re: Problem with the result set of postgres

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