Skip site navigation (1) Skip section navigation (2)

Re: DROP/CREATE

From: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>
To: "'Jean-Michel POURE'" <jm(dot)poure(at)freesurf(dot)fr>
Cc: pgadmin-hackers(at)postgresql(dot)org
Subject: Re: DROP/CREATE
Date: 2001-10-30 11:17:04
Message-ID: AA30E7BCCA5C1D4E88A231900F8325C00C1A@dogbert.vale-housing.co.uk (view raw or flat)
Thread:
Lists: pgadmin-hackers

> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm(dot)poure(at)freesurf(dot)fr] 
> Sent: 30 October 2001 11:08
> To: Dave Page
> Cc: pgadmin-hackers(at)postgresql(dot)org
> Subject: RE: DROP/CREATE
> 
> 
> 
> >Yes, I agree. As I said in my first message, there is no 
> problem with 
> >standalone views, but (and this is the killer) if your view is a 
> >dependency of something else like an SQL function or another 
> view then 
> >you have a problem. The problem is even bigger (i.e. harder 
> to detect) 
> >if the rowtype of the view is used as a parameter to or return value 
> >from a function (is this actually possible?  I know it is with a 
> >table).
> 
> Yes, it is possible, but functions treat views and table as 
> SQL. Remember, I learnt it from you !!! No dependency problem.
> 
> Ex: Create table1. Create function1 that outputs a value from 
> table1. Drop table1. Create table1. Run function1. It should work.
> 
> Needs some testing to verify.

You misunderstand me I think. In PostgreSQL a table row is a type itself,
therefore you can pass an entire tuple to or from a function (example from
pgAdmin I basSQL.bas). Note the function return value pgadmin_table_cache
which is a table:

CREATE FUNCTION pgadmin_get_rows(oid) RETURNS pgadmin_table_cache AS 'SELECT
DISTINCT ON(table_oid) * FROM pgadmin_table_cache WHERE table_oid = $1 ORDER
BY table_oid, table_timestamp DESC' LANGUAGE 'sql'"

This could easily be pl/pgsql rather than SQL, and whilst the function body
is OK as we discovered, what would happen in the above example if I added a
column to pgadmin_table_cache? And if pgadmin_table_cache was (if possible)
a view, then there is our problem.

Unfortunately I can't test this right now but iff you can I'd be interested
to hear what you find.

> >Incidently, pgAdmin II (and pgSchema) has no concept of 
> objects being 
> >defined on Views at present. I'll add that to my To-Do list - 
> >presumable it's only Rules (and Triggers?).
> 
> Triggers for 100% sure. We could automate the creation of 
> triggers for view 
> updating/deleting.
> Highly wanted cool feature.

Agreed, very cool. Added to the list.

pgadmin-hackers by date

Next:From: Jean-Michel POUREDate: 2001-10-30 12:06:44
Subject: Re: [HACKERS] Serious performance problem
Previous:From: Jean-Michel POUREDate: 2001-10-30 11:07:38
Subject: Re: DROP/CREATE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group