Thread: timestamp value

timestamp value

From
Ivan Horvath
Date:
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

Re: timestamp value

From
"Josh Berkus"
Date:
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

Re: timestamp value

From
Ivan Horvath
Date:
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

Re: timestamp value

From
Oliver Elphick
Date:
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


Re: timestamp value

From
Ivan Horvath
Date:
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

Re: timestamp value

From
Oliver Elphick
Date:
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


Re: timestamp value

From
Ivan Horvath
Date:
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