Thread: timestamp value
Hi All, i created a table, which has a field; type is timestamp to store the record creation/modification value. if i fill this table from a stored procedure the field value is always the SAME! please help me. Ivan __________________________________________________ Do You Yahoo!? Yahoo! Greetings - Send FREE e-cards for every occasion! http://greetings.yahoo.com
Ivan, > i created a table, which has a field; type is > timestamp to store the record creation/modification > value. > > if i fill this table from a stored procedure the field > value is always the SAME! Please provide copies of the table and function schema. You've obviously made a mistake in your syntax, but nobody can help without specifics. -Josh
Thanks for answer,
here is the code of the stored procedure:
DROP FUNCTION a_function();
CREATE FUNCTION a_function () RETURNS interval AS '
DECLARE
process_time interval;
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO unit (unit_name, mod_date)
VALUES (''a'', current_timestamp);
END LOOP;
select into process_time max(mod_date) -
min(mod_date) from unit;
RETURN process_time;
END;'
LANGUAGE 'plpgsql';
and here is the the table structure:
DROP SEQUENCE seq_unit;
CREATE SEQUENCE seq_unit;
DROP TABLE unit;
CREATE TABLE unit (
unit_id int4 NOT NULL default nextval('seq_unit')
CONSTRAINT unit_pk PRIMARY KEY,
unit_name varchar(10) NOT NULL CHECK (unit_name <>
''),
mod_user varchar(10) NOT NULL DEFAULT
current_user(),
mod_date timestamp(0) NOT NULL
);
thank you
Ivan
--- Josh Berkus <josh@agliodbs.com> wrote:
> Ivan,
>
> > i created a table, which has a field; type is
> > timestamp to store the record
> creation/modification
> > value.
> >
> > if i fill this table from a stored procedure the
> field
> > value is always the SAME!
>
> Please provide copies of the table and function
> schema. You've
> obviously made a mistake in your syntax, but nobody
> can help without
> specifics.
>
> -Josh
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - sign up for Fantasy Baseball
http://sports.yahoo.com
On Mon, 2002-03-04 at 07:45, Ivan Horvath wrote:
> Thanks for answer,
>
> here is the code of the stored procedure:
>
> DROP FUNCTION a_function();
>
> CREATE FUNCTION a_function () RETURNS interval AS '
In 7.2 you can now do CREATE OR REPLACE FUNCTION... rather than DROP
followed by CREATE.
> DECLARE
> process_time interval;
> BEGIN
> FOR i IN 1..10000 LOOP
> INSERT INTO unit (unit_name, mod_date)
> VALUES (''a'', current_timestamp);
> END LOOP;
>
> select into process_time max(mod_date) -
> min(mod_date) from unit;
>
> RETURN process_time;
>
> END;'
> LANGUAGE 'plpgsql';
All operations within a plpgsql procedure happen within a transaction
and within a transaction current_timestamp does not change (by design).
So each separate call to this procedure will produce 10000 identical
inserts.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Give, and it will be given to you. A good measure,
pressed down, taken together and running over,
will be poured into your lap. For with the same
measure that you use, it will be measured to
you." Luke 6:38
Oliver,
then what can i do -regarding timestamp- if i need to
create such a routine, so called "batch-processing",
which writes data to a table, or more tables at the
same time?
how can i ask the real timestamp value within a stored
procedure?
Ivan
--- Oliver Elphick <olly@lfix.co.uk> wrote:
> On Mon, 2002-03-04 at 07:45, Ivan Horvath wrote:
> > Thanks for answer,
> >
> > here is the code of the stored procedure:
> >
> > DROP FUNCTION a_function();
> >
> > CREATE FUNCTION a_function () RETURNS interval AS
> '
>
> In 7.2 you can now do CREATE OR REPLACE FUNCTION...
> rather than DROP
> followed by CREATE.
>
> > DECLARE
> > process_time interval;
> > BEGIN
> > FOR i IN 1..10000 LOOP
> > INSERT INTO unit (unit_name, mod_date)
> > VALUES (''a'', current_timestamp);
> > END LOOP;
> >
> > select into process_time max(mod_date) -
> > min(mod_date) from unit;
> >
> > RETURN process_time;
> >
> > END;'
> > LANGUAGE 'plpgsql';
>
>
> All operations within a plpgsql procedure happen
> within a transaction
> and within a transaction current_timestamp does not
> change (by design).
> So each separate call to this procedure will produce
> 10000 identical
> inserts.
>
> --
> Oliver Elphick
> Oliver.Elphick@lfix.co.uk
> Isle of Wight
> http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A
> 614D 4C34 3E1D 0C1C
>
> "Give, and it will be given to you. A good
> measure,
> pressed down, taken together and running over,
>
> will be poured into your lap. For with the
> same
> measure that you use, it will be measured to
> you." Luke 6:38
>
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - sign up for Fantasy Baseball
http://sports.yahoo.com
On Mon, 2002-03-04 at 09:49, Ivan Horvath wrote: > Oliver, > > then what can i do -regarding timestamp- if i need to > create such a routine, so called "batch-processing", > which writes data to a table, or more tables at the > same time? > how can i ask the real timestamp value within a stored > procedure? 1. Do you really need to distinguish _within_a_transaction_? 2. Use timeofday()::TIMESTAMP (It's in the docs at User manual 4.8.3) -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Give, and it will be given to you. A good measure, pressed down, taken together and running over, will be poured into your lap. For with the same measure that you use, it will be measured to you." Luke 6:38
thank you very much it is working! Ivan --- Oliver Elphick <olly@lfix.co.uk> wrote: > On Mon, 2002-03-04 at 09:49, Ivan Horvath wrote: > > Oliver, > > > > then what can i do -regarding timestamp- if i need > to > > create such a routine, so called > "batch-processing", > > which writes data to a table, or more tables at > the > > same time? > > how can i ask the real timestamp value within a > stored > > procedure? > > > 1. Do you really need to distinguish > _within_a_transaction_? > > 2. Use timeofday()::TIMESTAMP > (It's in the docs at User manual 4.8.3) > > -- > Oliver Elphick > Oliver.Elphick@lfix.co.uk > Isle of Wight > http://www.lfix.co.uk/oliver > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A > 614D 4C34 3E1D 0C1C > > "Give, and it will be given to you. A good > measure, > pressed down, taken together and running over, > > will be poured into your lap. For with the > same > measure that you use, it will be measured to > you." Luke 6:38 > __________________________________________________ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.com