0

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?

0

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.

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.