Re: delay of function - Mailing list pgsql-novice

[Please copy the mailing list on replies.]

On Thu, Mar 02, 2006 at 11:00:00AM +0100,  Jaromr Kamler wrote:
> >Do you have a requirement to provide metadata that's not available
> >via the system catalogs? Is there a reason basic_metadata can't
> >be a view? Are you familiar with how views work?
>
> I don't need know only size of table, but I need know accurancy
> (complex statistic function) and extend (functions) of spatial data
> and etc. like I writed. For detection this, I must call some PostGIS
> functions on geometry column (common column the_geom) after inserted
> all records. Is view able do this?

If you can write a query to return what you want then you can put
that query in a view.  However, if the query is expensive then the
view will be expensive because each time you select from the view
the database will run the query.

> I need table (or view) like this: CREATE TABLE basic_metadata
> (name text, owner name, accurancy real, north real, west real, east
> real, south real, ...). North...south is extend of map. Is view
> able do this? I am afraid, that it will by slowly when I watch view,
> there si many of spatial tables with many GB. I thing that table
> is better solution ... .

You might be interested in reading about materialized views.
PostgreSQL doesn't provide them natively but it does provide the
mechanisms to implement them.

http://www.jonathangardner.net/PostgreSQL/materialized_views/matviews.html

You might also find the PostGIS function estimated_extent() useful
for determining the extent of a geometry column, and you can use
Gevel to examine GiST indexes.

http://postgis.refractions.net/docs/ch06.html#id2793703
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Gevel

If you have questions about examining PostGIS data and indexes then
I'd recommend asking in postgis-users where the PostGIS developers
hang out.

> > The database can't possibly know when you're done inserting records
> > so you'll have to call the function yourself with SELECT.
>
> I can't call function in my own by SELECT every time, when someone
> insert inside data storage new table. Maybe I can use for this Linux
> daemon cron.

You might be able to use triggers; see the materialized views link
above for some ideas.  However, if every insert fires an expensive
trigger then you might have performance problems.  If the information
you're after is expensive to obtain and subject to change, then a
cron job might be the way to go.

--
Michael Fuhr