From: Sugandha Shah <Sugandhas@cybage.com>
To: Janning Vygen <vygen@gmx.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Few Queries
Date: Wed, 14 Aug 2002 17:59:26 +0530
Message-ID: <027601c2438e$3a6304a0$2005a8c0@cybage.com> (raw)
References: <01b301c24352$12570fb0$2005a8c0@cybage.com>
<200208140702.g7E72Cd05129@janning.planwerk6.local>
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@gmx.de>
To: "Sugandha Shah" <Sugandhas@cybage.com>; <pgsql-sql@postgresql.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@postgresql.org
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Sugandhas@cybage.com, vygen@gmx.de
Subject: Re: Few Queries
In-Reply-To: <027601c2438e$3a6304a0$2005a8c0@cybage.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox