From ch@rodos.fzk.de Thu Dec 12 13:41:03 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 5C060476CD2 for ; Thu, 12 Dec 2002 08:41:03 -0500 (EST) Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id ED52E476D8D for ; Thu, 12 Dec 2002 08:39:42 -0500 (EST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Thu Dec 12 14:37:38 2002 +0100 Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id OAA14045; Thu, 12 Dec 2002 14:37:33 +0100 (MET) Message-ID: <3DF8911D.DAFAEF09@rodos.fzk.de> Date: Thu, 12 Dec 2002 14:37:33 +0100 From: Christoph Haller X-Mailer: Mozilla 4.06 [en] (X11; I; HP-UX B.10.20 9000/780) MIME-Version: 1.0 To: atul@imcindia.net Cc: pgsql-sql@postgresql.org Subject: Re: Stored Procedure Problem Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/154 X-Sequence-Number: 10742 > CREATE FUNCTION b_function() RETURNS int4 AS ' > DECLARE > an_integer int4; > BEGIN > select emp_id from employee; > return an_integer; > END; > ' > LANGUAGE 'plpgsql'; > Try SELECT INTO an_integer emp_id from employee; Regards, Christoph From atul@imcindia.net Thu Dec 12 13:31:27 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BA327475EF0 for ; Thu, 12 Dec 2002 08:31:27 -0500 (EST) Received: from ns2.imcindia.net (unknown [203.129.231.83]) by postgresql.org (Postfix) with ESMTP id CF778476003 for ; Thu, 12 Dec 2002 08:31:24 -0500 (EST) Received: from virgo ([203.129.231.85]) by ns2.imcindia.net (8.9.3/8.9.3) with SMTP id TAA30657 for ; Thu, 12 Dec 2002 19:14:18 +0530 From: "Atul" To: Subject: Stored Procedure Problem Date: Thu, 12 Dec 2002 19:11:36 +0530 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0004_01C2A212.4A6836D0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/153 X-Sequence-Number: 10741 This is a multi-part message in MIME format. ------=_NextPart_000_0004_01C2A212.4A6836D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit Hello, Atul Here, I have one problem while accessing Database Records Or Recordset from stored procedure. Procedure is like this, CREATE FUNCTION b_function() RETURNS int4 AS ' DECLARE an_integer int4; BEGIN select emp_id from employee; return an_integer; END; ' LANGUAGE 'plpgsql'; I create the procedure . After that I run the statement like Test=#> select b_function(); Error comes like Error: ERROR: SELECT query has no destination for result data. If you want to discard the results, use PERFORM instead. WARNING: Error occurred while executing PL/pgSQL function b_function WARNING: line 4 at SQL statement Also I use PERFORM statement. But still problem is not resolved. Please Help me. I hope your kind co-operation. Thanks. Atul… ------=_NextPart_000_0004_01C2A212.4A6836D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hello,

<= /span>

&nbs= p; Atul Here, I have one problem while accessing Database Records Or Recordset from stored procedure. Procedure is like this,<= /font>

<= /span>

= CREATE FUNCTION b_function() RETURNS int4 AS '

= DECLARE

= an_integer int4;

= BEGIN

= select emp_id from employee;

= return an_integer;

= END;

= '

= LANGUAGE 'plpgsql';

<= /span>

I create the procedure . After that I run the statement like

<= /span>

= Test=3D#> select b_function();

<= /span>

Error comes like

<= /span>

Er= ror: ERROR: SELECT query has no destination for result data.

&= nbsp; If you want to discard the results, use PERFORM instead.

WA= RNING: Error occurred while executing PL= /pgSQL function b_function

WA= RNING: line 4 at SQL statement

Also I use PERFORM statement. But still problem is not resolved.

<= /span>

Please Help me. I hope your kind co-operation.

<= /span>

Thanks.

<= /span>

Atul…

------=_NextPart_000_0004_01C2A212.4A6836D0-- From hhiturre@yahoo.com.ar Thu Dec 12 13:43:36 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 83D66476851 for ; Thu, 12 Dec 2002 08:43:36 -0500 (EST) Received: from web20106.mail.yahoo.com (web20106.mail.yahoo.com [216.136.226.43]) by postgresql.org (Postfix) with SMTP id 3477D4762C7 for ; Thu, 12 Dec 2002 08:43:29 -0500 (EST) Message-ID: <20021212134332.42226.qmail@web20106.mail.yahoo.com> Received: from [200.45.99.243] by web20106.mail.yahoo.com via HTTP; Thu, 12 Dec 2002 10:43:32 ART Date: Thu, 12 Dec 2002 10:43:32 -0300 (ART) From: =?iso-8859-1?q?H=E9ctor=20Iturre?= Subject: Re: Stored Procedure Problem To: Atul In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/155 X-Sequence-Number: 10743 Hi, use this CREATE FUNCTION b_function() RETURNS int4 AS ' DECLARE an_integer int4; BEGIN select into an_integer emp_id from employee; return an_integer; END; ' LANGUAGE 'plpgsql'; --- Atul escribió: > Hello, > > Atul Here, I have one problem while > accessing Database > Records Or Recordset from stored procedure. > Procedure is like this, > > CREATE FUNCTION b_function() RETURNS int4 AS ' > DECLARE > an_integer int4; > BEGIN > select emp_id from employee; > return an_integer; > END; > ' > LANGUAGE 'plpgsql'; > > I create the procedure . After that I run the > statement like > > Test=#> select b_function(); > > Error comes like > > Error: ERROR: SELECT query has no destination for > result data. > If you want to discard the results, > use PERFORM instead. > WARNING: Error occurred while executing PL/pgSQL > function b_function > WARNING: line 4 at SQL statement > > Also I use PERFORM statement. But still problem is > not resolved. > > Please Help me. I hope your kind co-operation. > > Thanks. > > Atul… > Ahora podés usar Yahoo! Messenger desde tu celular. Aprendé cómo hacerlo en Yahoo! Móvil: http://ar.mobile.yahoo.com/sms.html From atul@imcindia.net Thu Dec 12 14:33:40 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8F333476D57 for ; Thu, 12 Dec 2002 09:33:40 -0500 (EST) Received: from ns2.imcindia.net (unknown [203.129.231.83]) by postgresql.org (Postfix) with ESMTP id A101947638A for ; Thu, 12 Dec 2002 09:33:22 -0500 (EST) Received: from virgo ([203.129.231.85]) by ns2.imcindia.net (8.9.3/8.9.3) with SMTP id UAA30828 for ; Thu, 12 Dec 2002 20:16:04 +0530 From: "Atul" To: Subject: Re: Stored Procedure Problem Date: Thu, 12 Dec 2002 20:13:22 +0530 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000A_01C2A21A.EBA28020" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/158 X-Sequence-Number: 10746 This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C2A21A.EBA28020 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit Hi, How to return multiple columns through stored procedure. Consider EX. CREATE FUNCTION b_function() RETURNS varchar AS ' DECLARE an_integer int4; an_name varchar; BEGIN select into an_integer emp_id,an_name emp_name from employee; return an_integer,an_name; END; ' LANGUAGE 'plpgsql'; But This Gives Error(For Multiple column , not for single column) Please Let me know. Thanks Atul… ------=_NextPart_000_000A_01C2A21A.EBA28020 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi,

&nbs= p; How to return multiple columns through stored procedure.

Consider EX.

CREATE FUN= CTION b_function() RETURNS varchar AS '

DECLARE

an_integer int4;

an_name varchar;

BEGIN

select into an_integer emp_id,an_name emp_name from employee;

return an_integer,an_= name;

END;

LANGUAGE 'plpgsql';<= font size=3D2 color=3Dblack face=3D"Courier New">

<= /span>

But This Gives Error(For Multiple column , not for single column)

Please Let me know.

<= /span>

Thanks

<= /span>

Atul…

------=_NextPart_000_000A_01C2A21A.EBA28020-- From jasiek@klaster.net Thu Dec 12 14:49:55 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 1EBDE476879 for ; Thu, 12 Dec 2002 09:49:55 -0500 (EST) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by postgresql.org (Postfix) with ESMTP id D76CD4762C6 for ; Thu, 12 Dec 2002 09:49:19 -0500 (EST) Received: from klaster.net (pb73.krakow.cvx.ppp.tpnet.pl [213.76.37.73]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 16E762B266; Thu, 12 Dec 2002 15:34:05 +0100 (CET) Message-ID: <3DF8A280.1010107@klaster.net> Date: Thu, 12 Dec 2002 15:51:44 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US; rv:1.2b) Gecko/20021016 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Atul Cc: pgsql-sql@postgresql.org Subject: Re: Stored Procedure Problem References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/159 X-Sequence-Number: 10747 Atul wrote: > CREATE FUNCTION b_function() RETURNS varchar AS ' > > DECLARE > > an_integer int4; > > an_name varchar; > > BEGIN > > select into an_integer emp_id,an_name emp_name from employee; > > return an_integer,an_name; > > END; > > ' First: select into an_integer,an_name emp_id,emp_name... Second: you can't return 2 variables from plpgsql function. Tomasz Myrta From rmello@cc.usu.edu Thu Dec 12 16:33:21 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CEFD8475D6E for ; Thu, 12 Dec 2002 11:33:21 -0500 (EST) Received: from brasileiro.net (slxwy.dorms.usu.edu [129.123.227.56]) by postgresql.org (Postfix) with ESMTP id 186C7475CE7 for ; Thu, 12 Dec 2002 11:33:20 -0500 (EST) Received: from roberto by brasileiro.net with local (Exim 3.35 #1 (Debian)) id 18MWHD-000664-00; Thu, 12 Dec 2002 09:33:31 -0700 Date: Thu, 12 Dec 2002 09:33:31 -0700 From: Roberto Mello To: Atul Cc: pgsql-sql@postgresql.org Subject: Re: Stored Procedure Problem Message-ID: <20021212163330.GG22465@cc.usu.edu> References: Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4i X-message-flag: Please consider using an e-mail program other than Outlook. It's insecure. X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/162 X-Sequence-Number: 10750 On Thu, Dec 12, 2002 at 08:13:22PM +0530, Atul wrote: > Hi, > How to return multiple columns through stored procedure. > But This Gives Error(For Multiple column , not for single column) > Please Let me know. You didn't say which version of PostgreSQL you are using. In PG 7.2 you can return a cursor. In 7.3 you can return a cursor or a true record set. See http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html (true to 7.2 and 7.3) Also http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html ("Returning from a function"). The part on returning record sets is only relevant to 7.3 -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + :.:..::..:::.:::::..:..:.:..:..::::.:...:: => BRAILE TAGLINE! From mallah@trade-india.com Fri Dec 13 10:57:19 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0CF62475C8B for ; Fri, 13 Dec 2002 05:57:19 -0500 (EST) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id BDB8D476B15 for ; Fri, 13 Dec 2002 05:57:13 -0500 (EST) Received: (qmail 12976 invoked from network); 13 Dec 2002 10:48:12 -0000 Received: from unknown (HELO system67.trade-india-local.com) (192.168.0.67) by infocom-236-129-del.trade-india.com with SMTP; 13 Dec 2002 10:48:12 -0000 From: "Rajesh Kumar Mallah." Organization: Infocom Network Limited. To: Tomasz Myrta , Atul Subject: Re: Stored Procedure Problem Date: Fri, 13 Dec 2002 16:31:42 +0530 User-Agent: KMail/1.5 Cc: pgsql-sql@postgresql.org References: <3DF8A280.1010107@klaster.net> In-Reply-To: <3DF8A280.1010107@klaster.net> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200212131631.42051.mallah@trade-india.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/173 X-Sequence-Number: 10761 In 7.3 you can , in follwoing steps, 1. do a CREATE TYPE (i would recommend to use a sperate schema for storing user defined types) 2. in plpgsql declare the RECORD of that type . 3. populate the record varible according to your business logic and return the RECORD using RETURN statements. hope it will help , if not please revert back. regds mallah. On Thursday 12 December 2002 08:21 pm, Tomasz Myrta wrote: > Atul wrote: > > CREATE FUNCTION b_function() RETURNS varchar AS ' > > > > DECLARE > > > > an_integer int4; > > > > an_name varchar; > > > > BEGIN > > > > select into an_integer emp_id,an_name emp_name from employee; > > > > return an_integer,an_name; > > > > END; > > > > ' > > First: select into an_integer,an_name emp_id,emp_name... > Second: you can't return 2 variables from plpgsql function. > > Tomasz Myrta > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.