0

I have the following relations:

CREATE TABLE cities (
    city VARCHAR (80) PRIMARY KEY,
    LOCATION point
);

CREATE TABLE weather (
    city VARCHAR (80) REFERENCES cities (city),
    temp_lo INT,
    temp_hi INT,
    prcp REAL,
    DATE DATE
);

CREATE VIEW test AS SELECT
    *
FROM
    cities;

Now I would like to utilize the test view or any other view in a GUI which simply presents an editable table. This is possible with the recently introduced automatically updatable views. I want the table to contain drop-downs containing all possible values every time there is a column with constrains on it, like the column with a foreign key in this case.

How can this be achieved? I know that I could utilize enums and I do that today, but on Amazon RDS they are unreasonably hard to modify.

I had an idea of (ab)using the "EXPLAIN SELECT" query for each column and check which table and column is being queried.

1

You can check which tables are used by a view using this statement:

select vtu.table_schema, 
       vtu.table_name
from information_schema.view_table_usage vtu 
  join pg_class cl on cl.oid = (quote_ident(vtu.table_schema)||'.'||quote_ident(vtu.table_name))::regclass 
where (view_schema, view_name) = ('public', 'test');

If that returns only a single table, you can query the system for foreign keys referencing that (single) table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.