From: Joe Conway <mail@joeconway.com>
To: Sandeep Chibber <sandeep@vreach.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem with the result set of postgres
Date: Mon, 27 May 2002 16:18:06 -0700
Message-ID: <3CF2BEAE.7020207@joeconway.com> (raw)
References: <000f01c2054f$4d79ac10$1302a8c2@multicast.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mail@joeconway.com, sandeep@vreach.net
Subject: Re: Problem with the result set of postgres
In-Reply-To: <3CF2BEAE.7020207@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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