From: | csanyipal(at)gmail(dot)com |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: To create a Column ina Table with function |
Date: | 2014-08-20 10:29:19 |
Message-ID: | 87zjez8neo.fsf@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi Laurenz,
Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> csanyipal(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2014-08-20 13:46:36 | Re: To create a Column ina Table with function |
Previous Message | Albe Laurenz | 2014-08-20 06:58:30 | Re: To create a Column ina Table with function |