CODEWITHSUNDEEP
postgresqlpostgresql-9.3
MAK
MAK

Reputation: 7260

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

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

Upvotes: 0

Views: 119

Answers (1)

user330315
user330315

Reputation:

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?

Upvotes: 2

Related Questions