I am listing out the problem I am facing = while=20 EXECUTING the function .

create table newtest (name varchar(50), a= ddress=20 varchar(50))

CREATE FUNCTION newtestfunc () RETURNS se= tof=20 varchar AS
'SELECT name,address FROM newtest'
LANGUAGE 'SQL';<= /DIV>

select newtestfunc()=20

The output is 168269272 ,

in this table there are two records and t= he output=20 is also coming twice There is no change in the number 168269272=20 ,168269272

Please guide me how to get the resul= t=20 set through a java bean

My Requirement :

1.I want to retrieve multiple records from the tabl= e using=20 function. The function call will come from the java bean.

2. The table will have different datatypes.<= /DIV>

Thanks

------=_NextPart_000_000C_01C2057D.67072E90-- From mail@joeconway.com Mon May 27 23:54:18 2002 Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 7FB64476532 for ; 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 ; 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 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 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 From sandeep@vreach.net Tue May 28 09:15:45 2002 Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id E3C6A475F43 for ; Tue, 28 May 2002 05:15:45 -0400 (EDT) Received: from vreach.net (unknown [203.200.123.22]) by postgresql.org (Postfix) with ESMTP id 73DF8475AF2 for ; Tue, 28 May 2002 05:14:02 -0400 (EDT) Received: from lokhi [194.168.2.19] by vreach.mzi.net [194.168.2.30] with SMTP (MDaemon.PRO.v5.0.5.R) for ; Tue, 28 May 2002 14:42:58 +0530 Message-ID: <00bc01c20627$96dad450$1302a8c2@multicast.com> From: "Sandeep Chibber" To: Subject: Problem with the result set of postgres Date: Tue, 28 May 2002 14:41:02 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00B9_01C20655.B06CEAB0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-MDRemoteIP: 194.168.2.19 X-Return-Path: sandeep@vreach.net X-MDaemon-Deliver-To: pgsql-sql@postgresql.org X-Archive-Number: 200206/5 X-Sequence-Number: 7970 This is a multi-part message in MIME format. ------=_NextPart_000_00B9_01C20655.B06CEAB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 =3D $1 AND ICDN_EVENTREG.PASSWORD =3D $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 ------=_NextPart_000_00B9_01C20655.B06CEAB0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi ,

I want to have function which w= ill=20 have a query which will accept a
parameter from my application and on th= e=20 basis of the parameter retreive a
result set.

You have helped me = in=20 getting the result set but if want to pass a parameter
in the WHERE Clau= es ,i=20 have no idea.

Table structure is

CREATE TABLE ICDN_EVENTREG&n= bsp;=20 ( USER_ID VARCHAR(30) NOT NULL ,   PASSWORD
VARCHAR(16)= =20 ,   EMAIL VARCHAR(80) , DEPARTMENT VARCHAR(30) ,  = ;=20 FIRST_NAME
VARCHAR(30) , LAST_NAME VARCHAR(30) ,   = =20 DESIGNATION VARCHAR(30) , TYPE
VARCHAR(15) , STATUS VARCHAR(= 15)=20 DEFAULT 'Pending',DOJ TIMESTAMP) ;

Function body is as=20 bellow

CREATE FUNCTION tester5(varchar, varchar) RETURNS SETOF=20 ICDN_EVENTREG=20 AS
'SELECT
user_id,password,email,department,first_name,last_name,des= ignation,type,stat
us=20 FROM    ICDN_EVENTREG      &nb= sp;=20 WHERE     ICDN_EVENTREG.USER_ID =3D $1=20 AND
ICDN_EVENTREG.PASSWORD =3D $2 ' LANGUAGE 'sql';

I want to Kno= w=20 how
1. How to pass parameter to this function
2. How to call this=20 application
3. In this function i am selecting all the fields , if i wan= t to=20 select only
two or three tuples,then how to go about it
4. If have id= ea=20 about java bean then how to call his function from a Java
Bean.

T= hanks=20 for your prompt reply. You are saving me from lot of trouble.

Thanks= =20 again

Sandeep

------=_NextPart_000_00B9_01C20655.B06CEAB0--