Retrieving What variables mean when retrieving view definitions

table variableview

I'm working on a small script to transfer views over from one database to another. I can get the view definition by using select view_definition from information_schema.views where table_name = 'actor_info', however the result is 

SELECT a.actor_id,
       a.first_name,
       a.last_name,
       group_concat(DISTINCT (((c.name)::text || ': '::text)
       || ( SELECT group_concat((f.title)::text) AS group_concat
              FROM ((film f
                     JOIN film_category fc_1 ON ((f.film_id = fc_1.film_id)))
                    JOIN film_actor fa_1 ON ((f.film_id = fa_1.film_id)))
             WHERE ((fc_1.category_id = c.category_id) AND (fa_1.actor_id = a.actor_id))
             GROUP BY fa_1.actor_id))) AS film_info
  FROM (((actor a
          LEFT JOIN film_actor fa ON ((a.actor_id = fa.actor_id)))
         LEFT JOIN film_category fc ON ((fa.film_id = fc.film_id)))
        LEFT JOIN category c ON ((fc.category_id = c.category_id)))
 GROUP BY a.actor_id, a.first_name, a.last_name;

I'm looking for a way to figure which tables a and c refer to. Is there a SQL statement that can be used?

Best Answer

a and c are table aliases; they're defined in the FROM clause of the SELECT statement:

...
FROM (((actor a
        LEFT JOIN film_actor fa ON ((a.actor_id = fa.actor_id)))
       LEFT JOIN film_category fc ON ((fa.film_id = fc.film_id)))
      LEFT JOIN category c ON ((fc.category_id = c.category_id)))
...

a refers to actor, c to category, etc.

It's a little hard to follow until you notice that there's a sub-query involved inside the outermost group_concat function.

Related Question