Hi Everyone,

The purpose of this post is to document how to make PostgreSQL’s concat_ws behave like MySQL’s concat_ws. You can use the below functions bring multiple records into a single comma or new-line delimited string.

When I wrote this, I was using PostgreSQL version 8.4. At that time the concat_ws function did not exist; therefore, I created it myself. If you look at the below functions, you can see the modern function commented out.

If you need help writing queries or function for transactional applications like open source ERP (ADempiere or iDempiere), do not hesitate to contact me.

–step 1 create the concat function
CREATE OR REPLACE FUNCTION concat_ws_newline(text, text)
RETURNS text AS $$
case when $1 = ”
then $2
else $1 || ‘\n’ || $2 end
–SELECT concat_ws(‘\n’, $1, $2) if you are using postgresql 9.0 or better
$$ LANGUAGE sql;

–step 2 – create the aggregation function
CREATE AGGREGATE str_agg (text) (
sfunc = concat_ws_newline,
stype = text,
initcond = ”

–step 3 – test
select str_agg(value)
from m_product
where m_product_id in (7132247, 7132248, 7132249)

I hope this helps!!! If you are using ADempiere or iDempiere, open source ERP, you can use this function to show line detail summaries from document headers like Sales Orders, Purchase Orders, Material Receipts, Linked documents, etc…

Special thanks goes out to Joe Conway for his inspirational tutorial on PostgreSQL functions.

Why consider Open Source ERP

Open source ERP gives you every opportunity to prove or disprove its ability to support your company’s ERP needs on a timeline that satisfies your organizational needs. With open source ERP, you do not face the same financial constraints nor do you face the same conflicts of interest as with commercial ERP. Instead, you invest in the appropriate skills and knowledge for your people and processes. Best of all – if open source ERP cannot solve your company’s needs, you can safely justify spending the additional $2K to $5K per person per year for life of your commercial ERP to help drive your organization’s success.

ADempiere vs iDempiere vs Openbravo vs Compiere

Please note that ADempiere, iDempiere and Openbravo are forks or copies from Compiere. Therefore, they have similar abilities mentioned above. The biggest difference is that ADempiere and iDempiere are pure open source. There are no features held behind a commercial or paid license.

About Chuck Boecking: I am an ERP educator. I believe that open source ERP have achieved mainstream capabilities, and as a result, more companies can create greater efficiency across their organization. I started using the iDempiere code base in 2003. Back then, it was called Compiere. In 2006, I started my first multi-million dollar installation. Since then, ADempiere has helped me create great success with distribution and manufacturing companies all over the world. My vision of success is to find companies that can best use open source ERP to help them achieve a single, global instance that drives a discontinuous increase in profitability. I believe that organizations win when they own their technology.

If you have questions, comments or concerns, let me know. I definitely want your feedback.

You can contact me by phone using 512.850.6068.

My email is chuck@chuboe.com.

You can complete the form on this page.

Thank you for taking the time. I look forward to speaking with you.

Chuck Boecking




Leave a Reply

Your email address will not be published. Required fields are marked *