Re: Few Queries - Mailing list pgsql-sql
| From | Sugandha Shah |
|---|---|
| Subject | Re: Few Queries |
| Date | |
| Msg-id | 027601c2438e$3a6304a0$2005a8c0@cybage.com Whole thread Raw |
| In response to | Few Queries ("Sugandha Shah" <Sugandhas@cybage.com>) |
| Responses |
Re: Few Queries
(Oliver Elphick <olly@lfix.co.uk>)
Re: Few Queries (Richard Huxton <dev@archonet.com>) |
| List | pgsql-sql |
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;
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 '';
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;
END IF;
return true;
END;'
LANGUAGE 'plpgsql';
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
> > 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
>