Re: DROP/CREATE

> -----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.