Display colon separated values in postgres

I am using postgresql 8.1 and i dont have the new functions in that version. Please help me what to do in that case?

My first table is as follows

unit_office table:

Mandal_ids       Name
82:05:          test sample
08:20:16:       test sample

Mandal Master table:

mandal_id  mandal_name
08         Etcherla
16         Hiramandalam
20         Gara

Now when I say select * from unit_office it should display:

Mandal             Name of office
Hiramandalam, Gara   test sample

i.e in place of ids I want the corresponding names (which are in master table)separated by comma

I have a column in postgres which has colon separated ids. The following is one record from my table.

mandalid                
18:82:14:11:08:05:20:16:83:37:23:36:15:06:38:33:26:30:22:04:03:

When I say select * from table, the mandalid column should display the names of the mandals in the id place separated by a comma.

Now i have the corresponding name for the id in a master table.

I want to display the 开发者_高级运维names of the ids in the select query of the first table. like

my first table name is unit office. when i say select * from unit office, I want the names in the place of ids.

---

I suggest you redesign your tables, but if you cannot, then you may need to define a function, which will split the mandal_ids string into integers, and map them to names. I suggest you read the PostgreSQL documentation on creating functions. The "PL/pgSQL" language may be a good choice. You may use the functions string_to_array and array_to_string.

But if you can, I suggest you define your tables in the following way:

mandals:
  id  name
   16  Hiramandalam
   20  Gara

unit_offices:
  id  name
   1   test sample

mandals_in_offices:
  office_id mandal_id
   1         16
   1         20

The output from the following query should be what you need:

  SELECT string_agg(m.name,',') AS mandal_names,
         max(o.name) AS office_name
      FROM mandals_in_offices i
        INNER JOIN unit_offices o ON i.office_id = o.id
        INNER JOIN mandals m ON i.mandal_id = m.id
      GROUP BY o.id;

The function string_agg appeared in PostgreSQL version 9, so if you are using older version, you may need to write similar function yourself. I believe this will not be too hard.

---

Here's what we did in LedgerSMB:

1. created a function to do the concatenation
2. created a custom aggregate to do the aggregation.

This allows you to do this easily.

CREATE OR REPLACE FUNCTION concat_colon(TEXT, TEXT) returns TEXT as
$$
select CASE WHEN $1 IS NULL THEN $2 ELSE $1 || ':' || $2 END;
$$ language sql;

CREATE AGGREGATE concat_colon (
        BASETYPE = text,
        STYPE = text,
        SFUNC = concat_colon
);

Then you can:

select concat_colon(mycol::text) from mytable;

Works just fine.