INTERVAL in a function

Поиск
Список
Период
Сортировка
От Ron St-Pierre
Тема INTERVAL in a function
Дата
Msg-id 41900C26.1010500@syscor.com
обсуждение исходный текст
Ответы Re: INTERVAL in a function
Список pgsql-general
I have a simple function which I use to set up a users' expiry date. If
a field in a table contains an interval then
this function returns a timestamp some time in the future (usually two
weeks), null otherwise. I can't pass the
interval from the table into a variable properly within the function.
Any ideas?

CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS timestamp AS '

  DECLARE
    grpID ALIAS FOR $1;
    intval INTERVAL;
    exptime TIMESTAMP;
  BEGIN
    SELECT INTO intval unitTimeLength::INTERVAL FROM customer.groups WHERE groupsID = grpID;
    IF intval IS NULL THEN
      RETURN NULL;
    ELSE
      SELECT INTO exptime current_timestamp + INTERVAL ''intval'';
      RETURN exptime;
    END IF;
  END;
' LANGUAGE 'plpgsql';


SELECT getUnitTimeLength(55);

ERROR:  invalid input syntax for type interval: "intval"
CONTEXT:  PL/pgSQL function "getunittimelength" line 11 at select into variables


However if I change the else clause to this:
    ELSE
      SELECT INTO exptime current_timestamp;
      RETURN exptime;
    END IF;
it works:
----------------------------
 2004-11-08 16:14:40.273597
(1 row)


Thanks
Ron








В списке pgsql-general по дате отправления:

Предыдущее
От: "J. Michael Crawford"
Дата:
Сообщение: Re: [JDBC] Using Postgres with Latin1 (ISO8859-1)
Следующее
От: "Ed L."
Дата:
Сообщение: server auto-restarts and ipcs