Re: Restricting a VIEW. - Mailing list pgsql-sql
From | Terry Yapt |
---|---|
Subject | Re: Restricting a VIEW. |
Date | |
Msg-id | 1ae7rusjimt2155m7imhstij9iqenpr4bj@4ax.com Whole thread Raw |
In response to | Re: Restricting a VIEW. (Ludwig Lim <lud_nowhere_man@yahoo.com>) |
List | pgsql-sql |
Hi Ludwig, I need that like a VIEW because if I convert that view to a select, I must to do that SELECT in a lot of client programs. I would to know if I can to do any kind of VIEW with parameters... Rewriting the view in the example: DROP VIEW vw_ty_test; CREATE VIEW vw_ty_test AS SELECT TO_CHAR(datein,'MM/YYYY') AS datein2, SUM(money) FROM ty_test WHERE datein BETWEEN $Parameter1 AND $Parameter2 GROUP BY datein2; is it possible ?? How ? Thanks a lot... On Mon, 21 Oct 2002 02:51:10 +0000 (UTC), lud_nowhere_man@yahoo.com (Ludwig Lim) wrote: > >--- Terry Yapt <yapt@technovell.com> wrote: >> Hello all, >> >> I have a doubt. In the next example, I have a table >> with two columns: >> - DATE >> - MONEY >> >> And a VIEW which SUM's the money GROUPing by >> 'month/year' (I cut off the day)... >> >> Ok.. I would like to be able to SELECT * FROM VIEW.. >> but restricting by complete dates (dd/mm/yyyy)... >> (Last select in the example) >> >> I think it isn't possible, but I would like to know >> your opinion... Or if there is any workaround... >> >> Best regards.. >> >> --============================== >> DROP TABLE ty_test; >> CREATE TABLE ty_test >> (datein date NOT NULL, >> money numeric(6,2) NOT NULL, >> PRIMARY KEY (datein) >> ) WITHOUT OIDS; >> >> INSERT INTO ty_test VALUES ('2002/10/01',10); >> INSERT INTO ty_test VALUES ('2002/10/15',20); >> INSERT INTO ty_test VALUES ('2002/11/15',30); >> >> DROP VIEW vw_ty_test; >> CREATE VIEW vw_ty_test AS >> SELECT >> TO_CHAR(datein,'MM/YYYY') AS datein2, >> SUM(money) >> FROM >> ty_test >> GROUP BY >> datein2; >> >> SELECT * FROM ty_test; -- All rows from table. >> SELECT * FROM vw_ty_test; -- All rows from view. >> > I don't the work around using a view but you can do >it without using a view: > > SELECT > to_number(to_char(datein,'mm'),'99') as month, > to_number(to_char(datein,'yyyy'),'9999') as year, > SUM(money) > FROM ty_test > WHERE datein BETWEEN > to_date('01/10/2002','mm/dd/yyyy') AND > to_date('09/10/2002','mm/ddy/yyyy') > ORDER BY > to_number(to_char(datein,'mm'),'99') > to_number(to_char(datein,'yyyy'),'9999'); > > >ludwig. > >__________________________________________________ >Do you Yahoo!? >New DSL Internet Access from SBC & Yahoo! >http://sbc.yahoo.com > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly