Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9A2ED475806 for ; Tue, 13 Aug 2002 02:04:27 -0400 (EDT) Received: from herald.cybage.com (universe.cybage.com [202.54.41.226]) by postgresql.org (Postfix) with ESMTP id 7ACD6475531 for ; Tue, 13 Aug 2002 02:04:24 -0400 (EDT) Received: from Sugandha ([192.168.5.32]) by herald.cybage.com with Microsoft SMTPSVC(5.0.2195.4453); Tue, 13 Aug 2002 11:36:39 +0530 Message-ID: <002a01c2428f$96de9000$2005a8c0@cybage.com> From: "Sugandha Shah" To: Subject: Function error Date: Tue, 13 Aug 2002 11:36:39 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0027_01C242BD.B08AE520" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-OriginalArrivalTime: 13 Aug 2002 06:06:39.0859 (UTC) FILETIME=[96DC9430:01C2428F] X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/196 X-Sequence-Number: 9042 This is a multi-part message in MIME format. ------=_NextPart_000_0027_01C242BD.B08AE520 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi , I'm porting MS- SQL stored procedure to postgres . I'm getting this error : Error occurred while executing PL/pgSQL function sel_free_disk_space line 7 at SQL statement SELECT query has no destination for result data. If you want to discard the results, use PERFORM instead. Here is the MS -SQL stored procedure :=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20 CREATE procedure sel_free_disk_space @computer_id int, @letter char(1) as declare @free int select @free =3D [free]=20 from logical_drive=20 where computer_id =3D @computer_id=20 and letter =3D upper(@letter) if (@free is null) set @free =3D -1 return @free GO Here is the equivalent function for Postgres : CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer AS ' DECLARE=20 -- Declare variable to store the free space. free INTEGER; =20=20=20=20=20=20=20=20=20 BEGIN=20 select free from logical_drive where computer_id =3D $1 and letter =3D= upper($2); IF free IS NULL THEN RETURN -1; END IF; =20=20=20=20 RETURN free; END; 'LANGUAGE 'plpgsql'; I'm not able to understand what I'm missing ? Secondly is there any equivalent of exec for postgres ? Any help will be highly appreciated. Regards, -Sugandha ------=_NextPart_000_0027_01C242BD.B08AE520 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi ,

I'm porting MS- SQL stored procedure to = postgres=20 . I'm getting this error :

Error occurred while executing P= L/pgSQL=20 function sel_free_disk_space
line 7 at SQL statement
SELECT que= ry=20 has no destination for result data.
If you want to discard the results, = use=20 PERFORM instead.

Here is the MS -SQL stored proce= dure=20 : = ; &n= bsp; = ; = =20

CREATE procedure=20 sel_free_disk_space
   @computer_id    &nb= sp;=20 int,
  =20 @letter          =20 char(1)
as
   declare @free int

   select @free = =3D =20 [free]
      from logical_drive=20
      where computer_id =3D @computer_id=20
         and letter =3D=20 upper(@letter)

if (@free is=20 null)
set @free =3D -1

return @free
GO

Here is the equivalent function = for=20 Postgres :

CREATE FUNCTION sel_free_disk_space(int4= ,bpchar)=20 RETURNS integer AS '
DECLARE
  -- Declare variable t= o=20 store the free space.
         f= ree=20 INTEGER;

BEGIN
   select free = from=20 logical_drive where computer_id =3D $1 and letter =3D=20 upper($2);
   IF free IS NULL =20 THEN
       RETURN -1;
  &nbs= p;=20 END IF;

  RETURN free;
END;
'LANG= UAGE=20 'plpgsql';

I'm not able to understand what I'm miss= ing=20 ?

Secondly is there any equivalent of exec= for=20 postgres ?

Any help will be highly appreciated.

Regards,

-Sugandha

------=_NextPart_000_0027_01C242BD.B08AE520--