Using Eclipse for editing remote files with ssh/sftp.

Posted by sergey - 22/08/08 at 08:08:36 pm

It took me a while to figure out how to work with remote files on server using Eclipse. It seems most of Eclipse users are developing locally and upload already tested code to the server (maybe i’am wrong). But for me it is important to work directly with remote files.
While on linux you can open and edit any file on the server using fish:// (or something like this), on windows there is no such option… On windows there is an option to install some software like sftpDrive, but it costs some 40$…

So I first tried to use winscp and configure Eclipse as an editor, but it didn’t worked because every time i clicked a file it was opening another copy of Eclipse despite any configurations i tried.

Then i played with several Eclipse plugins like Eclipse SSH Console, SFTP Plug-in for Eclipse, and aptana’s “Syncronize feature”, but no of them gave me the result i expected. None of the plugins give the ability to browse/edit/save remote file.

And FINALLY i found Remote System Explorer that solves everything. You download the plugin an intall it, and then you get a small button “Remote Systems” at the bottom of your eclipse that allow you to connect to you servers directly from eclipse. Great success!!

Get list of databases/tables/views in Postgresql

Posted by sergey - 21/08/08 at 09:08:59 pm

Get list of databases -
select datname FROM pg_catalog.pg_database;

This displays tables, views, indexes and sequences except postgres internal tables/views
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Same, but only for tables
select c.relname FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid);

This shows table columns of a given table and their types -
SELECT
attname as Field,
(select typname from pg_type where oid=pga.atttypid) as Type
FROM pg_attribute pga
WHERE
attrelid=(select oid from pg_class where relname=$table) and
attisdropped=false and
attnum>0