Re: [SQL] date range to set of dates expansion

[hari . fuchs]

Gary Stainburn  writes:

> Hi,
>
> How can I expand a date range in a table to a set of date records?
>
> I have a table of availabilities thus:
>
>   Column   |Type | Modifiers
> ---+-+
>  aid   | integer | not null default 
> nextval('availability_aid_seq'::regclass)
>  asid  | integer | not null
>  asdate| date| not null
>  afdate| date| not null
>  adays | integer |
>  acomments | text|
>
>
> asdate is the start date
> afdate is the finish date
>
> How can I expand this to a set of
>
>   Column   |Type | Modifiers
> ---+-+
>  aid   | integer | not null 
>  asid  | integer | not null
>  adate| date| not null
>  acomments | text|
>
> i.e.
>
>  aid | asid |   asdate   |   afdate   | adays |  acomments
> -+--+++---+
>   12 |1 | 2007-08-11 | 2007-08-12 | 1 | Early finish Sunday
>
> Becomes
>
>  aid | asid |   asdate   |  acomments
> -+--++
>   12 |1 | 2007-08-11 | Early finish Sunday
>   12 |1 | 2007-08-12 | Early finish Sunday
>
> I have a function date_range to return a set of dates, but so far I can't get 
> a valid view to work.

Why don't you just use the built-in PostgreSQL function for that?

SELECT aid, asid,
   generate_series (asdate, afdate, INTERVAL '1 day')::date AS asdate,
   acomments
FROM tbl


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] date arithmetic with columns

[hari . fuchs]

Peter Faulks  writes:

> I have two columns in two distinct tables, one is the starting time of
> an event, timestamp without time zone. Data is the utc datetime (for
> sorting across time zones), the other is the number of minutes to add.

Maybe I'm missing something, but why don't you just use timestamp with
timezone instead?


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] How to limit access only to certain records?

[hari . fuchs]

Andreas  writes:

> Hi,
>
> is there a way to limit access for some users only to certain records?
>
> e.g. there is a customer table and there are account-managers.
> Could I limit account-manager #1 so that he only can access customers
> only acording to a flag?

Maybe something like the following:

CREATE TABLE test1 (
  id serial NOT NULL,
  val text NOT NULL,
  _user text NOT NULL,
  PRIMARY KEY (id)
);

COPY test1 (val, _user) FROM stdin;
for user1#1 user1
for user1#2 user1
for user2#1 user2
\.

CREATE VIEW test1v AS
SELECT id, val
FROM test1
WHERE _user = current_user;


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql