Thread: 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

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
obtainmercy, and find grace to help in      time of need."           Hebrews 4:16 
 

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

"Sugandha Shah" <Sugandhas@cybage.com> 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

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" <dev@archonet.com>
To: "Sugandha Shah" <Sugandhas@cybage.com>; "Janning Vygen" <vygen@gmx.de>
Cc: <pgsql-sql@postgresql.org>
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