Support for more PostgreSQL functions?

LabKey Support Forum
Support for more PostgreSQL functions? slangley  2011-07-13 16:02
Status: Closed
 
1. Could you add pass-through support for more PostgreSQL functions as in:

      postgresMethods.put("array_to_string",new PassthroughMethod("array_to_string",JdbcType.VARCHAR,2,2));
      postgresMethods.put("string_agg",new PassthroughMethod("string_agg",JdbcType.VARCHAR,2,2));

2. And one that's somewhat harder because it's using the JdbcType.ARRAY which you haven't used before:

        postgresMethods.put("array_agg",new PassthroughMethod("array_agg",JdbcType.ARRAY,1,1));

3. And possibly support for using the DISTINCT keyword inside of function calls like:

   SELECT array_agg(DISTINCT a.visit) FROM a GROUP BY visit;

Thanks.
 
 
Ben Bimber responded:  2011-07-13 16:04
is array_agg() basically the same as group_concat(DISTNICT a.visit)?
 
slangley responded:  2011-07-13 16:23
It seems to be very close. I'll see if I can work with that.
 
slangley responded:  2011-07-18 13:07
So I find that group_concat(DISTNICT ... does work if the query is made against a table or view in the built-in schema. However, if I attempt to use it against a table or view in an external database schema it fails with a:

 Schema 'core' not found.

Unfortunately, for me, I need to use it on an external database schema.
 
adam responded:  2013-02-28 12:55
I've changed the SQL that GROUP_CONCAT generates on PostgreSQL 8.4+ to use the built-in array_agg() function; this should make GROUP_CONCAT work on any external PostgreSQL data source (8.4 or greater). The sort option will no-op if our custom function isn't present, but the basic functionality should work.
 
slangley responded:  2013-02-28 13:01
Thanks Adam. I'll test it out sometime - but not right away.