Some useful postgresql trick (case: idempiere stuff)

I have this in my inbox few months ago but I think somebody may needed this.

--CARA MENCARI TABLE
SELECT * FROM pg_catalog.pg_tables where schemaname = ‘adempiere’;

-- CARA MENCARI VIEW
select * from pg_catalog.pg_views where schemaname ='adempiere‘;

-- CARA MENCARI FUNCTION
-- sekedar catatan, argumen type masih berupa oid
-- bisa dicari di table pg_catalog.pg_type
select COALESCE( CAST(proc.proname AS TEXT) || CAST (proc.proargnames AS TEXT), proc.proname||'()') function_name,
CAST(proc.proname AS TEXT) || ('(' || COALESCE(CAST (proc.proargtypes AS TEXT)|| ')', proc.proname||')')) function_name_type
,proc.*
from pg_catalog.pg_proc proc
JOIN pg_catalog.pg_namespace ns ON proc.pronamespace=ns.oid
JOIN pg_catalog.pg_roles auth ON auth.oid=proc.proowner
where ns.nspname='adempiere' ORDER BY proc.proname ;

-- CARA MELIHAT DAFTAR KOLOM TABLE
-- mirip seperti 'describe table' di mysql
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = 'ad_org';-- CARA MENGUBAH BESAR KOLOM TABLE TANPA MENGUBAH TIPE DATA

-- misalnya varchar(20) menjadi varchar (40)

-- walau sebenarnya bisa pakai ALTER TABLE [tablename] ALTER COLUMN [column] TYPE [datatype]

-- kadang diperlukan karena table tersebut dipakai oleh view atau rule

-- catatan: ukuran baru mesti ada '+4' untuk alasan legacy postgres

-- sumber acuan: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data

UPDATE pg_attribute SET atttypmod = 35+4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';

-- CARA MENYIMPAN HASIL QUERY KE CSV

Copy (Select * From foo) To '/tmp/test.csv' With CSV;

-- submer acuan: http://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file