Thread: Function error
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.
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 :
CREATE procedure sel_free_disk_space
@computer_id int,
@letter char(1)
as
declare @free int
@computer_id int,
@letter char(1)
as
declare @free int
select @free = [free]
from logical_drive
where computer_id = @computer_id
and letter = upper(@letter)
from logical_drive
where computer_id = @computer_id
and letter = upper(@letter)
if (@free is null)
set @free = -1
set @free = -1
return @free
GO
GO
Here is the equivalent function for Postgres :
CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer AS '
DECLARE
-- Declare variable to store the free space.
free INTEGER;
DECLARE
-- Declare variable to store the free space.
free INTEGER;
BEGIN
select free from logical_drive where computer_id = $1 and letter = upper($2);
IF free IS NULL THEN
RETURN -1;
END IF;
RETURN free;
END;
'LANGUAGE 'plpgsql';
select free from logical_drive where computer_id = $1 and letter = upper($2);
IF free IS NULL THEN
RETURN -1;
END IF;
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
Am Dienstag, 13. August 2002 08:06 schrieb Sugandha Shah: > 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. you need to SELECT INTO if you want to set a varaible in plpgsql try this: DECLARE var_free integer; -- var_free is used just to name it differently from the column name BEGIN SELECT INTO var_free free from logical_drive where computer_id = $1 and letter = upper($2); IF var_free THEN ... Janning > CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer > AS ' DECLARE > -- Declare variable to store the free space. > free INTEGER; > > BEGIN > select free from logical_drive where computer_id = $1 and > letter = upper($2); > IF free IS NULL THEN > RETURN -1; > END IF; > > 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 -- Planwerk 6 /websolutions Herzogstraße 86 40215 Düsseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de
Hi , Thanx a lot it worked . Is there any equivalent of dateadd function in postgres ? Regards, -Sugandha ----- Original Message ----- From: "Janning Vygen" <vygen@gmx.de> To: "Sugandha Shah" <Sugandhas@cybage.com>; <pgsql-sql@postgresql.org> Sent: Tuesday, August 13, 2002 12:31 PM Subject: Re: [SQL] Function error > Am Dienstag, 13. August 2002 08:06 schrieb Sugandha Shah: > > 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. > > you need to SELECT INTO if you want to set a varaible in plpgsql > > try this: > DECLARE > var_free integer; > -- var_free is used just to name it differently from the column name > BEGIN > SELECT INTO var_free > free from logical_drive where computer_id = $1 and > letter = upper($2); > IF var_free THEN > ... > > > Janning > > > CREATE FUNCTION sel_free_disk_space(int4,bpchar) RETURNS integer > > AS ' DECLARE > > -- Declare variable to store the free space. > > free INTEGER; > > > > BEGIN > > select free from logical_drive where computer_id = $1 and > > letter = upper($2); > > IF free IS NULL THEN > > RETURN -1; > > END IF; > > > > 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 > > -- > Planwerk 6 /websolutions > Herzogstraße 86 > 40215 Düsseldorf > > fon 0211-6015919 > fax 0211-6015917 > http://www.planwerk6.de >
> Hi , > > Thanx a lot it worked . > > Is there any equivalent of dateadd function in postgres ? I highly recommend you actually read the Postgres manual's entries on date and time manipulation. You can just add intervals to dates: SELECT datefield + INTERVAL '1 month'; Chris