Re: array_agg for 8.3 - Mailing list pgsql-general

Hi Jeff,

On Sun, 18 Jan 2009, Jeff Davis wrote:

> On Sun, 2009-01-18 at 16:52 -0500, Faheem Mitha wrote:
>> Hi Jeff,
>>
>> When I try to run array_agg.sql inside psql I get
>>
>> btsnp_test=# \i '/tmp/array_agg/array_agg.sql'
>> BEGIN
>> psql:/tmp/array_agg/array_agg.sql:5: ERROR:  could not access file
>> "$libdir/array_agg": No such file or directory
>
> What that means is that it's not really installed into the global
> postgresql instance. What did you do to install it?

I haven't installed it anywhere. It is trying to install to the system,
which is a no-no.

faheem@orwell:/tmp/array_agg$ make install
mkdir -p -- /usr/share/postgresql/8.3/contrib
mkdir: cannot create directory `/usr/share/postgresql/8.3/contrib':
Permission denied
make: *** [installdirs] Error 1

In any case, I don't have admin permissions on the machine I'm trying to
install it to.

I replaced '$libdir/array_agg' in the following text by the current
location of the shared library on the machine, namely
'/tmp/array_agg/array_agg', since the shared library file is
/tmp/array_agg/array_agg.so. From the documentation, it sounds like
'/tmp/array_agg/array_agg.so' would also work.

"CREATE OR REPLACE FUNCTION UNNEST(ANYARRAY) RETURNS SETOF anyelement
   AS '$libdir/array_agg','array_unnest' LANGUAGE 'C' IMMUTABLE;"

This appears to work. I get

btsnp_test=# \i array_agg.sql
BEGIN
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE AGGREGATE
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
COMMIT

The test code you have in README now runs. Is that Ok? If there is a
better approach to this, please let me know.

> Make sure you have pg_config in your PATH environment variable, change
> directory to /tmp/array_agg, and then run "make install". That should
> install it in the global postgresql instance, and then you can run the
> SQL file to install it in the specific database.

Thanks very much for your help.
                                                          Regards, Faheem.

************************************************************************
array_agg.sql
************************************************************************

BEGIN;

CREATE OR REPLACE FUNCTION UNNEST(ANYARRAY) RETURNS SETOF anyelement
   AS '$libdir/array_agg','array_unnest' LANGUAGE 'C' IMMUTABLE;
CREATE OR REPLACE FUNCTION ARRAY_AGG_TRANSFN(INT, ANYELEMENT) RETURNS INT
   AS '$libdir/array_agg' LANGUAGE 'C' IMMUTABLE;
CREATE OR REPLACE FUNCTION ARRAY_AGG_FINALFN(ANYELEMENT) RETURNS ANYARRAY
   AS '$libdir/array_agg' LANGUAGE 'C' IMMUTABLE;

CREATE AGGREGATE ARRAY_AGG(anyelement) (
   SFUNC     = ARRAY_AGG_TRANSFN,
   STYPE     = INT,
   FINALFUNC = ARRAY_AGG_FINALFN
);

--
-- We need to properly set the state type for array_agg to be
--   "internal", but that's impossible with regular SQL. So, we make the
--   changes in the catalog directly.
--

UPDATE pg_aggregate SET aggtranstype = 2281 WHERE aggfnoid = 'array_agg'::regproc;
UPDATE pg_proc SET prorettype = 2277 WHERE oid = 'array_agg'::regproc;
UPDATE pg_proc SET prorettype = 2281, proargtypes = '2281 2283' WHERE oid = 'array_agg_transfn'::regproc;
UPDATE pg_proc SET proargtypes = '2281' WHERE oid = 'array_agg_finalfn'::regproc;

COMMIT;