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;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
