Re: [SQL] how to store a query, that results in a table

From: Paul Wehr <paul(at)industrialsoftworks(dot)com>
To: Keith Wong <keith(at)e-magine(dot)com(dot)au>
Cc: Nelson <nbrito(at)cmet(dot)net>, Jie Liang <jliang(at)ipinc(dot)com>, "pgsql-general(at)hub(dot)org" <pgsql-general(at)hub(dot)org>, "pgsql-novice(at)hub(dot)org" <pgsql-novice(at)hub(dot)org>, "pgsql-sql(at)hub(dot)org" <pgsql-sql(at)hub(dot)org>
Subject: Re: [SQL] how to store a query, that results in a table
Date: 2000-09-23 14:08:35
Message-ID: 39CCB963.2C038BBE@industrialsoftworks.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice pgsql-sql

Summary: Proposed solution, and question on efficiency of technique

I don't know if this is what you are looking for, but I have a database where
I needed a relatively complex view (which I can do thanks to the expanded view
buffer in 7.0!, it didn't fit in 6.5.3), but I need to pass an "effective
date" to the view that needed to be in a range (so I couldn't just use a
column in one of the source tables) to get the results I want. My "solution"
was to come up with an "effective dates" table with one column (primary keyed)
that I can put the dates in. For example:

create table effective_date (date date, primary key (date) );

create view complex_view as select blah, ..... , effective_date.date
from tablea, tableb, tablec, effective_date
where tablea.foo=tableb.foo
....
and effective_date.date between tablec.start_date=tablec.end_date
;

then, when I want to select rows from the view, I have to INSERT the date I
want into the "effective_date" table (which is effectively my "parameter"),
then I can select it from the view. i.e.:

insert into effective_date values ('09/23/2000'); -- may "fail" if date is
already in the table, but if it is, who cares?

select *
from complex_view
where date='09/23/2000';

Now it would certainly be nicer if I could set some kind of global system
variable to the date, then reference that system variable in the view, but I
couldn't figure out any way to do it. If anyone in the know is screaming out
loud at this technique, please point me in the right direction, I would love
to be able to skip the "Insert into effective_date..." step.

-paul

Keith Wong wrote:

> This is not really possible with postgresql at the moment.
> Better off trying to work around, perhaps using a view. That way you have a
> way to change the select
> statement without actually modifying your client code.
>
> Keith.
>
> At 06:09 PM 22/09/2000 -0400, Nelson wrote:
> >thank you jie Liang for your response, but my problems are:
> >1. How to store a query in the database.
> >2. How to give a parameter from outside of database, for example:
> >select * from table1 where row1 = my_parameter_outside.
> >Give me an example please.
> >
> >

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Louis-David Mitterrand 2000-09-23 15:26:47 running maintenance tasks on DB
Previous Message Enrico Comini 2000-09-23 10:36:09 No Reload

Browse pgsql-novice by date

  From Date Subject
Next Message bubola 2000-09-23 14:42:47 add or subtract days from a 'date' in query
Previous Message Keith Wong 2000-09-23 01:14:29 Re: how to store a query, that results in a table

Browse pgsql-sql by date

  From Date Subject
Next Message Louis-David Mitterrand 2000-09-23 15:45:20 missing "day(s)" in interval (was: Convert from Seconds-Since-Epoch to Timestamp)
Previous Message Keith Wong 2000-09-23 01:14:29 Re: how to store a query, that results in a table