From: Terry Yapt <yapt@NOtechSPnovellAM.com>
Subject: Re: Restricting a VIEW.
Date: Mon, 21 Oct 2002 10:25:12 +0200
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 102
Message-ID: <1ae7rusjimt2155m7imhstij9iqenpr4bj@4ax.com>
References: <20021021025107.28093.qmail@web80311.mail.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
To: pgsql-sql@postgresql.org

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