pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with the result set of postgres
3+ messages / 2 participants
[nested] [flat]
* Problem with the result set of postgres
@ 2002-05-27 07:22 Sandeep Chibber <sandeep@vreach.net>
2002-05-27 23:18 ` Re: Problem with the result set of postgres Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sandeep Chibber @ 2002-05-27 07:22 UTC (permalink / raw)
To: pgsql-sql
Hi
I am listing out the problem I am facing while EXECUTING the function .
create table newtest (name varchar(50), address varchar(50))
CREATE FUNCTION newtestfunc () RETURNS setof varchar AS
'SELECT name,address FROM newtest'
LANGUAGE 'SQL';
select newtestfunc()
The output is 168269272 ,
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.
Thanks
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with the result set of postgres
2002-05-27 07:22 Problem with the result set of postgres Sandeep Chibber <sandeep@vreach.net>
@ 2002-05-27 23:18 ` Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 3+ messages in thread
From: Joe Conway @ 2002-05-27 23:18 UTC (permalink / raw)
To: Sandeep Chibber <sandeep@vreach.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Problem with the result set of postgres
@ 2002-05-28 09:11 Sandeep Chibber <sandeep@vreach.net>
0 siblings, 0 replies; 3+ messages in thread
From: Sandeep Chibber @ 2002-05-28 09:11 UTC (permalink / raw)
To: pgsql-sql
Hi ,
I want to have function which will have a query which will accept a
parameter from my application and on the basis of the parameter retreive a
result set.
You have helped me in getting the result set but if want to pass a parameter
in the WHERE Claues ,i have no idea.
Table structure is
CREATE TABLE ICDN_EVENTREG ( USER_ID VARCHAR(30) NOT NULL , PASSWORD
VARCHAR(16) , EMAIL VARCHAR(80) , DEPARTMENT VARCHAR(30) , FIRST_NAME
VARCHAR(30) , LAST_NAME VARCHAR(30) , DESIGNATION VARCHAR(30) , TYPE
VARCHAR(15) , STATUS VARCHAR(15) DEFAULT 'Pending',DOJ TIMESTAMP) ;
Function body is as bellow
CREATE FUNCTION tester5(varchar, varchar) RETURNS SETOF ICDN_EVENTREG AS
'SELECT
user_id,password,email,department,first_name,last_name,designation,type,stat
us FROM ICDN_EVENTREG WHERE ICDN_EVENTREG.USER_ID = $1 AND
ICDN_EVENTREG.PASSWORD = $2 ' LANGUAGE 'sql';
I want to Know how
1. How to pass parameter to this function
2. How to call this application
3. In this function i am selecting all the fields , if i want to select only
two or three tuples,then how to go about it
4. If have idea about java bean then how to call his function from a Java
Bean.
Thanks for your prompt reply. You are saving me from lot of trouble.
Thanks again
Sandeep
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-05-28 09:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-27 07:22 Problem with the result set of postgres Sandeep Chibber <sandeep@vreach.net>
2002-05-27 23:18 ` Joe Conway <mail@joeconway.com>
2002-05-28 09:11 Problem with the result set of postgres Sandeep Chibber <sandeep@vreach.net>
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