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:
- created a function to do the concatenation
- 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.
精彩评论