Few Queries

От:

"Sugandha Shah" <Sugandhas@cybage.com>

Дата:

14 августа 2002 г. в 01:16:34

Hi ,

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

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 ' ' $var_history_age_limit day' ';

     --begin transaction
    delete from history where complete_time <= set_time;
END IF;

return 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 del_old_history

NOTICE: line 8 at assignment

2. Is there any equiavlent of MS -SQLServer 'trancount ' in postgres ?

3. if object_id ('database_info') is null

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

Any help will be highly appreciated.

Regards,

-Sugandha

Re: Few Queries

От:

"Sugandha Shah" <Sugandhas@cybage.com>

Дата:

14 августа 2002 г. в 08:27:11

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
>