Hi ,

I'm still facing few problems and hence = the query=20 . I have searched the archives as well as read the manual.

1. I 'm firing a query and it returns th= e value=20 in variable which I need to pass to other query . Is this a right way= to=20 pass the value ? I'm newbie to this Database and hence f= acing=20 lot of syntax problems.

CREATE FUNCTION del_old_history() RETURN= S bool 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)  =20 THEN
      set_time :=3D select current_date()+= =20 INTERVAL ' ' $var_history_age_limit day' ';

     --begin=20 transaction
    delete from history where complete_time &= lt;=3D=20 set_time;
END IF;

ret= urn=20 true;

END;'
LANGUAGE 'plpgsql';

I get this error :

Error: ERROR: parser: parse error at or near "select"

NOTICE: Error occurred while executing PL/pgSQL function=20 del_old_history

NOTICE: line 8 at assignment

2. Is there any equiavlent of MS -S= QLServer=20 'trancount ' in postgres ?

3. if object_id ('database_info') is null= =20

how is above=20 statement verified in postgres . I tried looking for OID .

Any help will be highly=20 appreciated.

Regards,

-Sugandha

------=_NextPart_000_01A8_01C2437E.5E698870-- From vygen@gmx.de Wed Aug 14 07:02:14 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2D07A475935 for ; Wed, 14 Aug 2002 03:02:14 -0400 (EDT) Received: from issv0171.isis.de (issv0171.isis.de [195.158.131.223]) by postgresql.org (Postfix) with SMTP id D702A4754A8 for ; Wed, 14 Aug 2002 03:02:12 -0400 (EDT) Received: (qmail 7206 invoked by uid 1010); 14 Aug 2002 07:02:11 -0000 Received: from unknown (HELO janning.planwerk6.local) ([195.158.137.172]) (envelope-sender ) by mail.isis.de (qmail-ldap-1.03) with SMTP for ; 14 Aug 2002 07:02:11 -0000 Received: from there (localhost [127.0.0.1]) by janning.planwerk6.local (8.11.6/8.11.6/SuSE Linux 0.5) with SMTP id g7E72Cd05129; Wed, 14 Aug 2002 09:02:12 +0200 Message-Id: <200208140702.g7E72Cd05129@janning.planwerk6.local> Content-Type: text/plain; charset="iso-8859-1" From: Janning Vygen Organization: Planwerk 6 /websolutions To: "Sugandha Shah" , Subject: Re: Few Queries Date: Wed, 14 Aug 2002 09:02:12 +0200 X-Mailer: KMail [version 1.3.1] References: <01b301c24352$12570fb0$2005a8c0@cybage.com> In-Reply-To: <01b301c24352$12570fb0$2005a8c0@cybage.com> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/213 X-Sequence-Number: 9059 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 From Sugandhas@cybage.com Wed Aug 14 12:27:09 2002 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-- From olly@lfix.co.uk Wed Aug 14 13:10:48 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id F06DC4759A6 for ; Wed, 14 Aug 2002 09:10:48 -0400 (EDT) Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by postgresql.org (Postfix) with ESMTP id E2C82475902 for ; Wed, 14 Aug 2002 09:10:47 -0400 (EDT) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by anchor-post-33.mail.demon.net with esmtp (Exim 3.35 #1) id 17exvF-0007st-0X; Wed, 14 Aug 2002 14:10:50 +0100 Received: from localhost ([127.0.0.1] helo=localhost.localdomain ident=olly) by linda.lfix.co.uk with esmtp (Exim 3.35 #1 (Debian)) id 17exvC-0007ti-00; Wed, 14 Aug 2002 14:10:46 +0100 Subject: Re: Few Queries From: Oliver Elphick To: Sugandha Shah Cc: Janning Vygen , pgsql-sql@postgresql.org In-Reply-To: <027601c2438e$3a6304a0$2005a8c0@cybage.com> References: <01b301c24352$12570fb0$2005a8c0@cybage.com> <200208140702.g7E72Cd05129@janning.planwerk6.local> <027601c2438e$3a6304a0$2005a8c0@cybage.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 14 Aug 2002 14:10:46 +0100 Message-Id: <1029330646.28727.22.camel@linda> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/225 X-Sequence-Number: 9071 On Wed, 2002-08-14 at 13:29, Sugandha Shah wrote: > 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'; You don't seem to be returning what you have selected. I would expect "RETURN true;" to give you an error. Shouldn't that be "RETURN var_history_age_limit;"? -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Let us therefore come boldly unto the throne of grace, that we may obtain mercy, and find grace to help in time of need." Hebrews 4:16 From dev@archonet.com Wed Aug 14 13:45:02 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DC0FB475CEF for ; Wed, 14 Aug 2002 09:45:02 -0400 (EDT) Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by postgresql.org (Postfix) with ESMTP id 5984B475C65 for ; Wed, 14 Aug 2002 09:45:02 -0400 (EDT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-35.mail.demon.net with esmtp (Exim 3.36 #2) id 17eySO-0005rP-0U; Wed, 14 Aug 2002 14:45:05 +0100 Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id F23FF140BB; Wed, 14 Aug 2002 14:34:18 +0100 (BST) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: "Sugandha Shah" , "Janning Vygen" Subject: Re: Few Queries Date: Wed, 14 Aug 2002 14:34:16 +0100 User-Agent: KMail/1.4.1 Cc: References: <01b301c24352$12570fb0$2005a8c0@cybage.com> <200208140702.g7E72Cd05129@janning.planwerk6.local> <027601c2438e$3a6304a0$2005a8c0@cybage.com> In-Reply-To: <027601c2438e$3a6304a0$2005a8c0@cybage.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200208141434.16778.dev@archonet.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/227 X-Sequence-Number: 9073 On Wednesday 14 Aug 2002 1:29 pm, Sugandha Shah wrote: > Hi , > > No luck . Even with Select Into . Please if any body has faced similar > problem and knows a solution. You don't need select into here - the sample below deletes everything older= =20 than one day. You should be able to adapt it to your needs. Your example ha= d=20 a + rather than a - which would delete things in the future, that might hav= e=20 been what you intended, or it might not. The RAISE NOTICE line prints a useful debug value. I like to scatter these= =20 around while I'm testing. CREATE FUNCTION del_old_history() RETURNS int4 AS ' DECLARE var_history_age_limit int4; set_time timestamp; BEGIN var_history_age_limit:=3D1; set_time :=3D current_date() - (var_history_age_limit::text || '' days= =20 '')::interval; RAISE NOTICE ''set time =3D %'',set_time; delete from history where complete_time <=3D set_time; return var_history_age_limit; END;' LANGUAGE 'plpgsql'; -- Richard Huxton From tgl@sss.pgh.pa.us Wed Aug 14 14:21:31 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CA0974754A8 for ; Wed, 14 Aug 2002 10:21:31 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 882A04762EE for ; Wed, 14 Aug 2002 10:21:30 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g7EELXVk007916; Wed, 14 Aug 2002 10:21:33 -0400 (EDT) To: "Sugandha Shah" Cc: pgsql-sql@postgresql.org Subject: Re: Few Queries In-reply-to: <01b301c24352$12570fb0$2005a8c0@cybage.com> References: <01b301c24352$12570fb0$2005a8c0@cybage.com> Comments: In-reply-to "Sugandha Shah" message dated "Wed, 14 Aug 2002 10:35:54 +0530" Date: Wed, 14 Aug 2002 10:21:32 -0400 Message-ID: <7915.1029334892@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/230 X-Sequence-Number: 9076 "Sugandha Shah" writes: > 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 ' ' $var_history_age_limit day' '; You don't use a $ to refer to plpgsql variables (except for parameters, and there the *name* of the parameter is actually $n). Also ":= select" is redundant; I believe the correct syntax would be set_time := current_date() + ... or at least it would be if SQL expected current_date to come with parentheses, but it doesn't, so the next bug is that you need set_time := current_date + ... Another problem is that "INTERVAL ''var_history_age_limit day''" isn't going to work because plpgsql doesn't do textual interpolation of variables into queries. (If you want a language where that's how it's done, try pltcl or plperl.) While you could hack around with something like "CAST(text(var_history_age_limit) || ' day' as interval)", this actually is very much the hard way to do it --- adding an integer to a date already does what you want. So this statement should just be set_time := current_date + var_history_age_limit; although given the logic used later I wonder whether what you are after isn't really set_time := current_date - var_history_age_limit; BTW I'd declare set_time as timestamp or timestamptz if I were you; datetime is an obsolete datatype name that's not going to be accepted anymore as of 7.3. regards, tom lane From Sugandhas@cybage.com Mon Aug 19 07:27:27 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4381347597B for ; Mon, 19 Aug 2002 03:27:27 -0400 (EDT) Received: from herald.cybage.com (universe.cybage.com [202.54.41.226]) by postgresql.org (Postfix) with ESMTP id 6B2BA47591C for ; Mon, 19 Aug 2002 03:27:23 -0400 (EDT) Received: from Sugandha ([192.168.5.32]) by herald.cybage.com with Microsoft SMTPSVC(5.0.2195.4453); Mon, 19 Aug 2002 12:59:48 +0530 Message-ID: <099401c24752$327ae380$2005a8c0@cybage.com> From: "Sugandha Shah" To: "Richard Huxton" Cc: References: <01b301c24352$12570fb0$2005a8c0@cybage.com> <200208140702.g7E72Cd05129@janning.planwerk6.local> <027601c2438e$3a6304a0$2005a8c0@cybage.com> <200208141434.16778.dev@archonet.com> Subject: Re: Few Queries Date: Mon, 19 Aug 2002 12:59:47 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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: 19 Aug 2002 07:29:48.0104 (UTC) FILETIME=[3290DC80:01C24752] X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/262 X-Sequence-Number: 9108 Hello Richard, I'm really thankful to you. It worked. Somehow I wasn't getting it to work. This line did the trick. set_time := current_date() - (var_history_age_limit::text || '' days '')::interval; Regards, -Sugandha ----- Original Message ----- From: "Richard Huxton" To: "Sugandha Shah" ; "Janning Vygen" Cc: Sent: Wednesday, August 14, 2002 7:04 PM Subject: Re: [SQL] Few Queries On Wednesday 14 Aug 2002 1:29 pm, Sugandha Shah wrote: > Hi , > > No luck . Even with Select Into . Please if any body has faced similar > problem and knows a solution. You don't need select into here - the sample below deletes everything older than one day. You should be able to adapt it to your needs. Your example had a + rather than a - which would delete things in the future, that might have been what you intended, or it might not. The RAISE NOTICE line prints a useful debug value. I like to scatter these around while I'm testing. CREATE FUNCTION del_old_history() RETURNS int4 AS ' DECLARE var_history_age_limit int4; set_time timestamp; BEGIN var_history_age_limit:=1; set_time := current_date() - (var_history_age_limit::text || '' days '')::interval; RAISE NOTICE ''set time = %'',set_time; delete from history where complete_time <= set_time; return var_history_age_limit; END;' LANGUAGE 'plpgsql'; -- Richard Huxton ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org