PostgreSQL 9.3: Get list of table names present in view using function

Question

I want to get list of table names present in the view.

So I have created the function with one parameter(view_name) to get table names.

Function : funtion_GetTables_FromView

CREATE OR REPLACE FUNCTION funtion_GetTables_FromView
(
    view_Name varchar
)

RETURNS TABLE
(
    TableNames varchar
) AS

$BODY$

DECLARE 
    v_SQL varchar;

 BEGIN

    v_SQL := 'SELECT Table_Name  
             FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
             WHERE View_Name = ''' ||  view_Name || '''';

    RAISE INFO '%',v_SQL;

    RETURN QUERY EXECUTE v_SQL;
END;

$BODY$

LANGUAGE PLPGSQL;

Calling function:

select * from funtion_GetTables_FromView('myview');

But getting an error:

ERROR:  structure of query does not match function result type

You are using a parameter v_viewname that is no declared in the function header. Also (although, unrelated): you don't need dynamic SQL or PL/pgSQL for this, you can write a simple SQL function that just runs the query and uses the parameter where view_name = $1. E.g.: sqlfiddle.com/#!15/eb27f/1 — user330315
– user330315, Commented Dec 28, 2015 at 10:39
Try - hastebin.com/qoronuqite.sql or alter your existing function like this- hastebin.com/qawocevazu.pas — Vivek S.
– Vivek S., Commented Dec 28, 2015 at 10:46
@wingedpanther: when using a SQL function the cast isn't actually necessary. Apparently SQL has different (automatic) casting rules than dynamic SQL in PL/pgSQL. — user330315
– user330315, Commented Dec 28, 2015 at 10:53

score 2 · Accepted Answer · 2015-12-30 18:47:34Z

2

You only showed a part of the error message, the complete error message gives the actual reason:

ERROR: structure of query does not match function result type
Detail: Returned type information_schema.sql_identifier does not match expected type character varying in column 1.
Where: PL/pgSQL function funtion_gettables_fromview(character varying) line 14 at RETURN QUERY

So the column information_schema.table_name is not a varchar column. The immediate fix for this is to cast the column to the required type:

v_SQL := 'SELECT Table_Name::varchar
         FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
         WHERE View_Name = ''' ||  view_Name || '''';

But the whole function is needlessly complex and error prone. A simply SQL function will do just fine:

CREATE OR REPLACE FUNCTION funtion_GetTables_FromView(v_viewname varchar)
  RETURNS TABLE(tablenames varchar) 
AS
$$
  SELECT table_name  
  FROM information_schema.view_table_usage
  WHERE view_Name = v_viewname;
$$
LANGUAGE sql;

For some reason this does not require the cast to varchar. I suspect this has something to do with running dynamic SQL inside PL/pgSQL.

Unrelated, but: I personally find it pretty useless to prefix a function with function_. It is obvious when using it, that it is a function. Are you prefixing all your classes with class_ and all your methods with method_ in your programming language?

edited Dec 30, 2015 at 18:47

answered Dec 28, 2015 at 10:50

user330315

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

3 Comments

MAK Over a year ago

Thank you so much for detail explanation and too in minimum time. :)

MAK Over a year ago

Nope! I am not doing that in my work place. That is just for an example. But next time take care of that too.

Vlastimil Ovčáčík Over a year ago

You say it is not varchar column, but my findings says the opposite

select data_type from information_schema.columns where table_schema='information_schema' and table_name='view_table_usage' and column_name='table_name';

.

Collectives™ on Stack Overflow

PostgreSQL 9.3: Get list of table names present in view using function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related