PostgreSQL 9.3: Count all tables contained in view

Question

I want to count the number of tables which is contained by a view.

In SQL Server I do it like this:

Declare v_tables int

SELECT  v_Tables = Count(*)
      FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
      WHERE View_Name = View1;

How about PostgreSQL 9.3 ?

Yes, it's possible, because Postgresql has INFORMATION_SCHEMA. Are you wanting this in a stored procedure? — mlinth
– mlinth, Commented Jan 27, 2015 at 12:38

Patrick · Accepted Answer · 2015-01-27 13:03:08Z

1

CREATE OR REPLACE FUNCTION count_tables(p_viewname text) RETURNS integer AS $BODY$
  SELECT count(*) FROM information_schema.view_table_usage
  WHERE view_name = p_viewname;
$BODY$ LANGUAGE sql STABLE STRICT;

edited Jan 27, 2015 at 13:03

Patrick

33k7 gold badges73 silver badges102 bronze badges

answered Jan 27, 2015 at 12:53

mlinth

3,1586 gold badges35 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL 9.3: Count all tables contained in view

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related