Good to know: PostgreSQL records to single JSON string

To convert a set of results to a single JSON string, use:

SELECT
ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(ROW)))
as DAT FROM (
     [ your query here ]
) ROW;

Tip: In the query, if you specify field names or table.*, you will not get an object hence the JSON will not have your schema sub grouped by object name. i.e.

SELECT
ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(ROW)))
as DAT FROM (
     SELECT mytable WHERE [ ... ]
) ROW;

 ... is not the same as ...

SELECT
ARRAY_TO_JSON(ARRAY_AGG(ROW_TO_JSON(ROW)))
as DAT FROM (
     SELECT mytable.* WHERE [ ... ]
) ROW;



Kudos: http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql

Script to remove jpg files recursively

I am using a webcam which uploads files via FTP. Unfortunately the cam which uploads the files does not clean up old files. It's actually a set of cameras which all write file to their own directory structures. So when I need a script to clean up the old jpg files it needs to run recursively:

 #!/usr/bin/env python  
 import re  
 import os  
 __dir__ = os.path.dirname(os.path.realpath(__file__))  
 def cleanup_recursive(_dir=__dir__):  
   print "Cleaning " + _dir  
   for _item in os.listdir(_dir):  
     _path = _dir + "/" + _item  
     if os.path.isfile(_path) and re.match(".+\.jpg$", _path):  
       os.remove(_path)  
     elif os.path.isdir(_path):  
       cleanup_recursive(_dir)  
 cleanup_recursive()  

Remove Bitdefender from OSX (App Store version)

Bitdefender for Windows is a highly rated application, but I can tell you from personal experience, on OSX, it's pretty much garbage. Furthermore, the App Store version does not come with an uninstaller the same way the download (from their site) version does. After following their recommended way of removing the app you may want to remove other files also. Remove this folder: ~/Library/Application\ Support/Bitdefender\ Virus\ Scanner And this file: ~/Library/Saved\ Application\ State/com.bitdefender.BitdefenderVirusScanner.savedState The issue that I, and other nay sayers on the App Store, have with BD AV is that it completely locks up your system when running a full scan, essentially making it about as bad as some viruses. I'm using ClamXAV now instead.

PostgreSQL:: Find functions containing

Sometimes when you need to find a function which contains specific text in the definition or description, it's handy to have a function that makes that possible.

Here is a function that will allow you to do just that:

 /**  
 DEFINE  
  */  
 CREATE OR REPLACE FUNCTION my_admin_schema.sel_function_containing(_schema_name text, _search_value text)  
  RETURNS TABLE (  
   schema text, sproc_name text, arg_names text, return_type text, description text, definition text  
  ) AS  
  $BODY$  
 BEGIN  
  RETURN QUERY   
   WITH funcs AS (  
     SELECT  
       n.nspname::text AS schema  
      ,proname::text AS sproc_name  
      ,proargnames::text AS arg_names  
      ,t.typname::text AS return_type  
      ,d.description  
      ,pg_get_functiondef(p.oid) as definition  
     FROM pg_proc p  
      JOIN pg_type t on p.prorettype = t.oid  
      JOIN pg_description d on p.oid = d.objoid  
      JOIN pg_namespace n on n.oid = p.pronamespace  
     WHERE n.nspname = _schema_name  
   )  
   SELECT *  
   FROM funcs  
   WHERE funcs.definition ~* _search_value  
     OR funcs.description ~* _search_value  
   ;  
 END; $BODY$ LANGUAGE plpgsql STABLE COST 100  
 ;;  
 /**  
 EXAMPLE USAGE  
  */  
 SELECT * FROM offerpoint_admin1.sel_function_containing('my_app_schema', 'status')  
 ;;