FAQ
I am trying to use the EXTRACT function in pl/pgsql and running into a problem.

This is the command:

SELECT EXTRACT(EPOCH FROM TIMESTAMP variable_name) INTO Temp;

The variable_name is of type text (I've also tried it as type timestamp), but the parser breaks when it attempts to perform this function. It works great when I substitute the variable_name with '2004-1-10 00:00:00' , however, when I use a variable, it doesn't.

Any one have any ideas on how I can get variable substitution to work in my function using EXTRACT?

Thanks,
Derrick

Search Discussions

  • Stephan Szabo at Mar 26, 2004 at 6:56 pm

    On Fri, 19 Mar 2004, Derrick Betts wrote:

    I am trying to use the EXTRACT function in pl/pgsql and running into a problem.

    This is the command:

    SELECT EXTRACT(EPOCH FROM TIMESTAMP variable_name) INTO Temp;

    The variable_name is of type text (I've also tried it as type
    timestamp), but the parser breaks when it attempts to perform this
    function. It works great when I substitute the variable_name with
    '2004-1-10 00:00:00' , however, when I use a variable, it doesn't.
    TIMESTAMP <foo> is meant for timestamp literals. If you want to
    convert a value from one type to another, you probably want something
    like CAST(variable_name AS TIMESTAMP)
  • Tom Lane at Mar 26, 2004 at 7:19 pm

    "Derrick Betts" <Derrick@Blue-Axis.com> writes:
    This is the command:
    SELECT EXTRACT(EPOCH FROM TIMESTAMP variable_name) INTO Temp;
    You're confusing the EXTRACT function with the notation for a literal
    constant of a specific type. The function is just

    EXTRACT(EPOCH FROM timestamp-expression)

    An example that involves a literal constant could be written either

    EXTRACT(EPOCH FROM TIMESTAMP '2004-1-10 00:00:00')
    EXTRACT(EPOCH FROM '2004-1-10 00:00:00'::TIMESTAMP)

    but when you are dealing with a variable you don't use the TIMESTAMP
    decoration, because the parser already knows what datatype the variable
    is. So

    EXTRACT(EPOCH FROM timestamp-variable)

    regards, tom lane
  • Bruno Wolff III at Mar 26, 2004 at 8:15 pm

    On Fri, Mar 19, 2004 at 07:44:25 -0700, Derrick Betts wrote:
    I am trying to use the EXTRACT function in pl/pgsql and running into a problem.

    This is the command:

    SELECT EXTRACT(EPOCH FROM TIMESTAMP variable_name) INTO Temp;

    The variable_name is of type text (I've also tried it as type timestamp), but the parser breaks when it attempts to perform this function. It works great when I substitute the variable_name with '2004-1-10 00:00:00' , however, when I use a variable, it doesn't.

    Any one have any ideas on how I can get variable substitution to work in my function using EXTRACT?
    I don't think typename variable is a valid operation. You would need to use
    CAST or :: to do a typecast. Probably you should just leave out "TIMESTAMP"
    from your example.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMar 19, '04 at 2:44p
activeMar 26, '04 at 8:15p
posts4
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase