Обсуждение: To create a Column ina Table with function

Hi,

I have a table with columns: number, date-range.
I wish to get in this table more columns: week-day-begin, week-day-end.

In the week-day-begin column I wish to get automatically the weekday
name of the first date in the date range.

In the week-day-end column I wish to get automatically the weekday
name of the last date in the date range.

How can I get these two columns by executing a function?
Should this function be a Dynamic Command, or a trigger?

Regards, from Pal

csanyipal@gmail.com wrote:
> I have a table with columns: number, date-range.
> I wish to get in this table more columns: week-day-begin, week-day-end.
> 
> In the week-day-begin column I wish to get automatically the weekday
> name of the first date in the date range.
> 
> In the week-day-end column I wish to get automatically the weekday
> name of the last date in the date range.
> 
> How can I get these two columns by executing a function?
> Should this function be a Dynamic Command, or a trigger?

If you really need those columns materialized, it would be a trigger
BEFORE INSERT OR UPDATE FOR EACH ROW.

But why not use a view? It is normally a bad idea to keep
redundant information around.

CREATE TABLE test (i integer PRIMARY KEY, dr daterange NOT NULL);

CREATE VIEW testview AS
   SELECT i, dr,
          EXTRACT(DOW FROM lower(dr)) AS wd_start,
          EXTRACT(DOW FROM upper(dr)) AS wd_end
   FROM test;

INSERT INTO test VALUES (1, '[1968-10-20,2050-04-01]');

SELECT * FROM testview;

 i |           dr            | wd_start | wd_end
---+-------------------------+----------+--------
 1 | [1968-10-20,2050-04-02) |        0 |      6
(1 row)

Yours,
Laurenz Albe

Hi Laurenz,

Albe Laurenz <laurenz.albe@wien.gv.at> writes:

> csanyipal@gmail.com wrote:
>> I have a table with columns: number, date-range.
>> I wish to get in this table more columns: week-day-begin, week-day-end.
>>
>> In the week-day-begin column I wish to get automatically the weekday
>> name of the first date in the date range.
>>
>> In the week-day-end column I wish to get automatically the weekday
>> name of the last date in the date range.
>>
>> How can I get these two columns by executing a function?
>> Should this function be a Dynamic Command, or a trigger?
>
> If you really need those columns materialized, it would be a trigger
> BEFORE INSERT OR UPDATE FOR EACH ROW.
>
> But why not use a view? It is normally a bad idea to keep
> redundant information around.
>
> CREATE TABLE test (i integer PRIMARY KEY, dr daterange NOT NULL);
>
> CREATE VIEW testview AS
>    SELECT i, dr,
>           EXTRACT(DOW FROM lower(dr)) AS wd_start,
>           EXTRACT(DOW FROM upper(dr)) AS wd_end
>    FROM test;
>
> INSERT INTO test VALUES (1, '[1968-10-20,2050-04-01]');
>
> SELECT * FROM testview;
>
>  i |           dr            | wd_start | wd_end
> ---+-------------------------+----------+--------
>  1 | [1968-10-20,2050-04-02) |        0 |      6
> (1 row)

Thanks, but this gives no names of weekdays but just numbers.

How can I get the name ( eg. Monday ) of the weekday in this view?

Regards, from Pal

csanyipal@gmail.com wrote:
>>> I have a table with columns: number, date-range.
>>> I wish to get in this table more columns: week-day-begin, week-day-end.
>>>
>>> In the week-day-begin column I wish to get automatically the weekday
>>> name of the first date in the date range.
>>>
>>> In the week-day-end column I wish to get automatically the weekday
>>> name of the last date in the date range.

>> CREATE TABLE test (i integer PRIMARY KEY, dr daterange NOT NULL);
>>
>> CREATE VIEW testview AS
>>    SELECT i, dr,
>>           EXTRACT(DOW FROM lower(dr)) AS wd_start,
>>           EXTRACT(DOW FROM upper(dr)) AS wd_end
>>    FROM test;
>>
>> INSERT INTO test VALUES (1, '[1968-10-20,2050-04-01]');
>>
>> SELECT * FROM testview;
>>
>>  i |           dr            | wd_start | wd_end
>> ---+-------------------------+----------+--------
>>  1 | [1968-10-20,2050-04-02) |        0 |      6
>> (1 row)

> Thanks, but this gives no names of weekdays but just numbers.
> 
> How can I get the name ( eg. Monday ) of the weekday in this view?

Try something like a CASE clause:

CASE EXTRACT(DOW FROM lower(dr)) WHEN 0 THEN 'Sunday' WHEN 1 THEN 'Monday' ... END

Yours,
Laurenz Albe