Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 59F13475A1C for ; Wed, 14 Aug 2002 08:27:09 -0400 (EDT) Received: from herald.cybage.com (universe.cybage.com [202.54.41.226]) by postgresql.org (Postfix) with ESMTP id 4D8EC4759B5 for ; Wed, 14 Aug 2002 08:27:05 -0400 (EDT) Received: from Sugandha ([192.168.5.32]) by herald.cybage.com with Microsoft SMTPSVC(5.0.2195.4453); Wed, 14 Aug 2002 17:59:26 +0530 Message-ID: <027601c2438e$3a6304a0$2005a8c0@cybage.com> From: "Sugandha Shah" To: "Janning Vygen" Cc: References: <01b301c24352$12570fb0$2005a8c0@cybage.com> <200208140702.g7E72Cd05129@janning.planwerk6.local> Subject: Re: Few Queries Date: Wed, 14 Aug 2002 17:59:26 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0273_01C243BC.53EDEF10" 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: 14 Aug 2002 12:29:26.0319 (UTC) FILETIME=[3A5A03F0:01C2438E] X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/222 X-Sequence-Number: 9068 This is a multi-part message in MIME format. ------=_NextPart_000_0273_01C243BC.53EDEF10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi , No luck . Even with Select Into . Please if any body has faced similar pro= blem and knows a solution. CREATE FUNCTION del_old_history() RETURNS int4 AS ' declare var_history_age_limit int4; set_time datetime; BEGIN=20 select into var_history_age_limit history_age_limit from database_info; IF (var_history_age_limit is not null) THEN =20=20 --set_time :=3Dselect current_date()+ INTERVAL '1 day'); select into set_time current_date()+ INTERVAL ''$var_history_age_limi= t days ''; delete from history where complete_time <=3D set_time; END IF;=20=20=20 return true; END;' LANGUAGE 'plpgsql'; Regards, -Sugandha ----- Original Message -----=20 From: "Janning Vygen" To: "Sugandha Shah" ; 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 :=3D select current_date()+ INTERVAL ' ' >=20 > If you do a aselect you need select into. Normal assignment is only=20 > possible with simple expression. Try: >=20 > SELECT INTO set_time current_date()+ INTERVAL ' '; >=20 > > 2. Is there any equiavlent of MS -SQLServer 'trancount ' in > > postgres ? >=20 > you should only post one question per mail and i dont know waht=20 > trancount is.=20 >=20 > 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 >=20 > > 3. if object_id ('database_info') is null > > how is above statement verified in postgres . I tried looking > > for OID . >=20 > same as answer to question number 2.=20 > something like=20=20 > GET DIAGNOSTICS var_oid =3D RESULT_OID; > IF var_oid IS NULL THEN >=20 > janning >=20 > --=20 > PLANWERK 6 /websolutions > Herzogstra=DFe 86 > 40215 D=FCsseldorf >=20 > fon 0211-6015919 > fax 0211-6015917 > http://www.planwerk6.de >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >=20 ------=_NextPart_000_0273_01C243BC.53EDEF10 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi ,
 
No luck  . Even with Select Into . = Please if=20 any body has faced similar problem and knows a solution.
 
CREATE FUNCTION del_old_history() RETURN= S int4 AS=20 '
declare
   var_history_age_limit int4;
  =20 set_time    datetime;
 
BEGIN
select into=20 var_history_age_limit history_age_limit from database_info;
IF= =20 (var_history_age_limit is not null)   THEN
 =20
      --set_time :=3Dselect current_date()+ IN= TERVAL=20 '1 day');
      select into set_time=20 current_date()+ INTERVAL ''$var_history_age_limit  days=20 '';
     delete from his= tory=20 where complete_time <=3D set_time;
END IF;   
       ret= urn=20 true;
END;'
LANGUAGE 'plpgsql';
 
Regards,
-Sugandha
 
 
----- Original Message -----
From: "Janning Vygen" <vygen@gmx.de>
To: "Sugandha Shah" <Sugandhas@cybage.com>;= =20 <pgsql-sql@postgresql.org>
Sent: Wednesday, August 14, 2002 12:32= =20 PM
Subject: Re: [SQL] Few Queries

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