Lists: | pgsql-sql |
---|
From: | Louis-David Mitterrand <vindex(at)apartia(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | return a text agreggate from a subselect |
Date: | 2001-12-19 16:11:28 |
Message-ID: | 20011219161128.GB28569@apartia.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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)
From: | "Andrew G(dot) Hammond" <drew(at)xyzzy(dot)dhs(dot)org> |
---|---|
To: | Louis-David Mitterrand <vindex(at)apartia(dot)org> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: return a text agreggate from a subselect |
Date: | 2001-12-19 19:28:49 |
Message-ID: | 1008790130.855.21.camel@xyzzy |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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(at)xyzzy(dot)dhs(dot)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
From: | Archibald Zimonyi <archie(at)netg(dot)se> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Connections? |
Date: | 2001-12-20 08:56:07 |
Message-ID: | Pine.LNX.4.21.0112200953250.21206-100000@valdez.netg.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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?
Archie
From: | Jan Wieck <janwieck(at)yahoo(dot)com> |
---|---|
To: | Archibald Zimonyi <archie(at)netg(dot)se> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Connections? |
Date: | 2001-12-20 13:03:43 |
Message-ID: | 200112201303.fBKD3hp01657@saturn.jw.home |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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(at)Yahoo(dot)com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
From: | Terrence Brannon <metaperl(at)mac(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | should temporary tables show up in \dt as type "temporary" or as something? |
Date: | 2001-12-20 19:49:40 |
Message-ID: | B4A39EAE-F582-11D5-802C-003065C2A10C@mac.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
I guess it is a small issue because it will disappear as soon as
the session closes, but I was just curious.