pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFew Queries
7+ messages / 5 participants
[nested] [flat]
* Few Queries
@ 2002-08-14 05:05 Sugandha Shah <Sugandhas@cybage.com>
0 siblings, 2 replies; 7+ messages in thread
From: Sugandha Shah @ 2002-08-14 05:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Few Queries
@ 2002-08-14 07:02 Janning Vygen <vygen@gmx.de>
parent: Sugandha Shah <Sugandhas@cybage.com>
1 sibling, 1 reply; 7+ messages in thread
From: Janning Vygen @ 2002-08-14 07:02 UTC (permalink / raw)
To: Sugandha Shah <Sugandhas@cybage.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Few Queries
@ 2002-08-14 12:29 Sugandha Shah <Sugandhas@cybage.com>
parent: Janning Vygen <vygen@gmx.de>
0 siblings, 2 replies; 7+ messages in thread
From: Sugandha Shah @ 2002-08-14 12:29 UTC (permalink / raw)
To: Janning Vygen <vygen@gmx.de>; +Cc: 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;
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Few Queries
@ 2002-08-14 13:10 Oliver Elphick <olly@lfix.co.uk>
parent: Sugandha Shah <Sugandhas@cybage.com>
1 sibling, 0 replies; 7+ messages in thread
From: Oliver Elphick @ 2002-08-14 13:10 UTC (permalink / raw)
To: Sugandha Shah <Sugandhas@cybage.com>; +Cc: Janning Vygen <vygen@gmx.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Few Queries
@ 2002-08-14 13:34 Richard Huxton <dev@archonet.com>
parent: Sugandha Shah <Sugandhas@cybage.com>
1 sibling, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2002-08-14 13:34 UTC (permalink / raw)
To: Sugandha Shah <Sugandhas@cybage.com>; Janning Vygen <vygen@gmx.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Few Queries
@ 2002-08-14 14:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sugandha Shah <Sugandhas@cybage.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2002-08-14 14:21 UTC (permalink / raw)
To: Sugandha Shah <Sugandhas@cybage.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Few Queries
@ 2002-08-19 07:29 Sugandha Shah <Sugandhas@cybage.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 7+ messages in thread
From: Sugandha Shah @ 2002-08-19 07:29 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-08-19 07:29 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-14 05:05 Few Queries Sugandha Shah <Sugandhas@cybage.com>
2002-08-14 07:02 ` Janning Vygen <vygen@gmx.de>
2002-08-14 12:29 ` Sugandha Shah <Sugandhas@cybage.com>
2002-08-14 13:10 ` Oliver Elphick <olly@lfix.co.uk>
2002-08-14 13:34 ` Richard Huxton <dev@archonet.com>
2002-08-19 07:29 ` Sugandha Shah <Sugandhas@cybage.com>
2002-08-14 14:21 ` Tom Lane <tgl@sss.pgh.pa.us>
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