Monday 3 October 2011

Helping Query for PostgreSQL DBA

How to get column list of a table. 

BEGIN;
CREATE AGGREGATE core.array_acc(anyelement) (
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = '{}'
);

 SELECT relname, array_acc(attname)
    FROM pg_attribute a join pg_class c on a.attrelid = c.oid
  WHERE relname in ('session', 'hits', 'conversion_hits')
        and attname not in ('tableoid','cmax','xmax','cmin','xmin','ctid')
GROUP BY relname;
commit;

relname   | conversion_hits
array_acc |{session_id,permanent_id,conversion_id,sale,price,iso_currency_id,order_code,description,sku,supersedes,user_defined1,user_defined2,user_defined3,user_defined4,document_url,referrer,domain_id,rdate,rtime,last_modified,client_biz_unit,client_section_name,old_price,old_currency_id}
-[ RECORD 2 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
relname   | hits
array_acc | {session_id,document_url,referrer,domain_id,rdate,rtime,last_modified,client_biz_unit,client_section_name}
-[ RECORD 3 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
relname   | sessions
array_acc | {session_id,permanent_id,entry_domain_id,host_ip,actual_ip,geo_worldregion_id,geo_country_id,geo_region_id,geo_location_id,campaign_id,account,category,item,traffic_source_id,keyword,paid_keyword,browser_id,browser_string,os_id,locale,language,returning_visitor,rdate,rtime,referrer,entry_url,latency_days,device_id,device_type_id,match_type_id,search_type_id,b2a_keyword_id,last_modified,redirect_id,media_published_site,media_creative_id,media_landing_page_id,client_section_name,client_biz_unit,track_url,search_engine_result_page,search_engine_position,ad_copy_id}

No comments:

Post a Comment