I want to retrieve the derivation expression of a column in a view, and I want to do it dynamically from within a stored procedure, so psql \d+ or pgAdmin is no use to me.

Where can I retrieve this information from? The full text is in pg_views but I'd rather not get into slicing this string up to try and find each of the column expressions.

  • can you give a specific example? not understanding what exactly you want to achieve. – Anup Shah Jan 31 '17 at 17:11
  • Can you mark the answer as chosen if you're satisified. – I Support The Boycott Feb 2 '17 at 19:24

From the docs

Views in PostgreSQL are implemented using the rule system.

So essentially, they get rewritten to simply SELECT statemetns. Nothing in the catalog stores the column expression. Internally, when you run \d on a view, it calls pg_catalog.pg_get_viewdef. You can't go further from that with the system catalog. You're going to have to parse that query to get to the column expression, perhaps with something like libpg_query.

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.