Alternative to SQL Server's sysobjects in PostgreSQL

Question

I am porting a database from SQL Server to PostgreSQL. Is there any alternative to sysobjects in PostgreSQL? If so, then what is it and how can be it used?

My view:

create view int_objects_v as 
  select
     io.*, soae."REQ_TYPE_NAME", soae."REQ_TABLE_NAME", 
     stc1."CODE_DESC" int_type_name, stc2."CODE_DESC" operation_type_name, 
     s."NAME" target_obj_name 
  from 
     int_objects io  
  left join 
     req_request_types_v soae on soae."ID" = io."SOURCE_OBJ_ID"  
  left join 
     std_type_codes_v stc1 on (stc1."CODE" = io."INT_TYPE" 
                               and stc1."TYPE" = 'intr_type') 
  left join
     std_type_codes_v stc2 on (stc2."CODE" = io."OPERATION_TYPE" 
                               and stc2."TYPE" = 'opr_type') 
  left join 
     sysobjects s on (s."ID" = io."TARGET_OBJ_ID") 
  where 
     io."ACTIVE_FLAG" = '1';

score 5 · Accepted Answer · edited May 23 '17 at 10:34

5

PostgreSQL supports the SQL-standard information_schema.

So does MSSQL, but it also supports sysobjects which is older equivalent for information_schema.

If you find a way to rewrite your query using information_schema terms, then you're all set.

Also look here INFORMATION_SCHEMA vs sysobjects

edited May 23 '17 at 10:34

Community

1
1

answered Oct 23 '12 at 06:27

mvp

111,019
13
122
148

2

BTW, if you can't get the desired information from `information_schema`, you can almost certainly get it from the PostgreSQL-specific `pg_catalog` tables. – Craig Ringer Oct 23 '12 at 08:58

Alternative to SQL Server's sysobjects in PostgreSQL

1 Answers1

Linked