PostgreSQL – How to Retrieve View Column Derivation in PostgreSQL or Greenplum

greenplumpostgresql

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.

Best Answer

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.

Related Solutions

Postgresql – What does “GIN doesn’t support full index scans” mean

a word or two belonging to a pre-set, almost 'constant', list of words.

Have you considered scanning the texts as they are input and flagging them (possibly with a many-to-many link to another table containing your list of keywords)?

Postgresql – Using dynamic column names in PostgreSQL

RETURN QUERY EXECUTE was introduced with Postgres 8.4.
Your version is just too old and unsupported by now. Upgrade to a more recent version.

Also, dynamic column names in the result are very hard to come by. It's a principle of SQL that it wants to know the return type - including the names - up front.

Returning anonymous records without a column definition list only works for a single record. Else you have to provide a column definition list in the call. Details under his question on SO:
Return multiple fields as a record in PostgreSQL with PL/pgSQL

There are limited ways around this with with polymorphic types. Advanced stuff:
Refactor a PL/pgSQL function to return the output of various SELECT queries

BTW, your function would look like this in modern PL/pgSQL:

CREATE OR REPLACE FUNCTION get_policy_name (
  _id        int,
  _lang      text,
  _def_value text
) RETURNS TABLE (col text) AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   format('SELECT COALESCE(%I, col, $1)
           FROM my_table WHERE id = $2'
         , 'col_' || _lang)
   USING _def_value, _id;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT col AS col_fr FROM get_policy_name (1, 'fr', 'foo');

Here, I am simply using a column alias in the call to achieve what you want. Much easier than dynamic column names ...

Best Answer

Related Solutions

Postgresql – What does “GIN doesn’t support full index scans” mean

Postgresql – Using dynamic column names in PostgreSQL

Related Question