Re: Few Queries

Hi ,

No luck . Even with Select Into . Please if any body has faced similar problem and knows a solution.

CREATE FUNCTION del_old_history() RETURNS int4 AS '
declare
var_history_age_limit int4;
set_time datetime;

BEGIN
select into var_history_age_limit history_age_limit from database_info;
IF (var_history_age_limit is not null) THEN

--set_time :=select current_date()+ INTERVAL '1 day');
select into set_time current_date()+ INTERVAL ''$var_history_age_limit days '';
delete from history where complete_time <= set_time;
END IF;
return true;
END;'
LANGUAGE 'plpgsql';

Regards,
-Sugandha

----- Original Message -----
From: "Janning Vygen" <vygen(at)gmx(dot)de>
To: "Sugandha Shah" <Sugandhas(at)cybage(dot)com>; <pgsql-sql(at)postgresql(dot)org>
Sent: Wednesday, August 14, 2002 12:32 PM
Subject: Re: [SQL] Few Queries

> Am Mittwoch, 14. August 2002 07:05 schrieb Sugandha Shah:
> > 1. I 'm firing a query and it returns the value in variable which I
> > need to pass to other query . Is this a right way to pass the
> > value ? I'm newbie to this Database and hence facing lot of
> > syntax problems.
> >
> > CREATE FUNCTION del_old_history() RETURNS bool AS '
> > declare
> > var_history_age_limit int4;
> > set_time datetime;
> > BEGIN
> > select into var_history_age_limit history_age_limit from
> > database_info; IF (var_history_age_limit is not null) THEN
> > set_time := select current_date()+ INTERVAL ' '
>
> If you do a aselect you need select into. Normal assignment is only
> possible with simple expression. Try:
>
> SELECT INTO set_time current_date()+ INTERVAL ' ';
>
> > 2. Is there any equiavlent of MS -SQLServer 'trancount ' in
> > postgres ?
>
> you should only post one question per mail and i dont know waht
> trancount is.
>
> do you mean something like getting the affected rows?
> Look at this file in the postgresdocs (7.2.1)
> plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
>
> > 3. if object_id ('database_info') is null
> > how is above statement verified in postgres . I tried looking
> > for OID .
>
> same as answer to question number 2.
> something like
> GET DIAGNOSTICS var_oid = RESULT_OID;
> IF var_oid IS NULL THEN
>
> janning
>
> --
> PLANWERK 6 /websolutions
> Herzogstraße 86
> 40215 Düsseldorf
>
> fon 0211-6015919
> fax 0211-6015917
> http://www.planwerk6.de
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>