Thread: return a text agreggate from a subselect

Hi,

I am trying to convert a subselect that has several matches into one
string comprising all matches concatenated:

The subselect: (SELECT company_name FROM table) 

The output I'd like: 'Company1|Company2|Company3|etc.'

Should I write a function or a new agreggate for this or is there is
simpler way?

Thanks in advance,

--    PANOPE: On dit même qu'au trône une brigue insolente           Veut placer Aricie et le sang de Pallante.
                               (Phèdre, J-B Racine, acte 1, scène 4)
 

On Wed, 2001-12-19 at 11:11, Louis-David Mitterrand wrote:

> The subselect: (SELECT company_name FROM table)
>
> The output I'd like: 'Company1|Company2|Company3|etc.'
>
> Should I write a function or a new agreggate for this or is there is
> simpler way?

I'm afraid the cleanest way to do this is with an aggregate.

CREATE FUNCTION barjoin(text, text) RETURNS text AS ' SELECT CASE WHEN length($1) > 0 THEN $1 || ''|'' || $2   ELSE $2
END;'LANGUAGE 'sql'; 

CREATE AGGREGATE barconcat(basetype=text, sfunc=barjoin,                           stype=text, initcond='');

SELECT barconcat(company_name) FROM table;

If you're in the mood to pointlessly performance tune the snot out of
it, you could implement barjoin to simply do $1|$2, and then write a
final function for the aggregate that trimmed off the beginning |...

--
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F
613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me

Hi everyone,

I get the following error:

psql: connectDBStart() -- connect() failed: Connection refused       Is the postmaster running locally       and
acceptingconnections on Unix socket '/tmp/.s.PGSQL.5432'?
 

when I try to connect after having started the postmaster. The 
problem is that unless I make /tmp writeable for everyone
postgres is not allowed to create the .s.PGSQL.5432 file. Is 
there any way to make postmaster create this file anywhere else?

Archie

Archibald Zimonyi wrote:
>
> Hi everyone,
>
> I get the following error:
>
> psql: connectDBStart() -- connect() failed: Connection refused
>         Is the postmaster running locally
>         and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'?
>
> when I try to connect after having started the postmaster. The
> problem is that unless I make /tmp writeable for everyone
> postgres is not allowed to create the .s.PGSQL.5432 file. Is
> there any way to make postmaster create this file anywhere else?
   The  /tmp  directory  is supposed to be writable by everyone,   and should have the sticky bit set.  Alot  of  unix
software  expects that, not only Postgres.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

I guess it is a small issue because it will disappear as soon as 
the session closes, but I was just curious.