I tried making an aggregate function that concatenates
any non-null strings it encounters, as shown below.
The function works fine on small tables; but when I run
it on one of my larger tables it seems to me that it
crashes the backend - though I don't see anything obvious
in the log.
According to 'top', the postmaster grows pretty quickly
so perhaps it ran out of memory? The query I ran is
doing a group-by and no individual value in the group
by clause should have that big a result; so hypothetically
it seems it wouldn't need to put everything in memory
if it sorted them first.
Any workarounds or other clever ways of doing this
kind of aggregation? I guess a stored procedure that
reads one group at a time?
Thanks in advance,
Ron
------------------------------------------------------------
--- The definition of my aggregte
------------------------------------------------------------
CREATE OR REPLACE FUNCTION nonull_append_strings (
text, text )
RETURNS text AS '
SELECT CASE WHEN $1 IS NULL THEN $2
WHEN $2 IS NULL THEN $1
ELSE $1 || '' '' || $2
END;
' LANGUAGE sql IMMUTABLE;
CREATE OPERATOR ||+ (
LEFTARG = TEXT,
RIGHTARG = TEXT,
PROCEDURE = nonull_append_strings
);
create aggregate strcat_agg (
sfunc = nonull_append_strings,
basetype = text,
stype = text
);
------------------------------------------------------------
--- Example using the function
------------------------------------------------------------
fli=#
fli=# create table new_keywords as
select ext_doc_id,strcat_agg(nam)||+strcat_agg(val)
from facet_raw group by ext_doc_id;
FATAL: terminating connection due to administrator command
CONTEXT: SQL function "nonull_append_strings" statement 1
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
fli=#
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
