Appending multiple row results in Postgresql

Question

I have a issue whether I'm facing an issue in concatenating multiple values using Cursor/ Function in postgreSQL.

I have a table names Products which contains several values of customers who buy different products,so there can be multiple entries for customers.

So my requirement is to get a part of an HTML if I give the customer email as parameter.

For example: If I give [email protected] which contains two entries in the table the output should be like shown below,

<p style="line-height: 14px; text- 
   align: center; font-size: 12px; margin: 0;"> product 1 </p>
<p style="line-height: 14px; text- 
    align: center; font-size: 12px; margin: 0;"> product 2 </p>

But now I'm getting the details of one product only,like this, product 1

CREATE OR REPLACE FUNCTION 
Append_Products(Customer_Email TEXT)
RETURNS text AS $$
DECLARE 
rowcount  BIGINT;
Products TEXT DEFAULT '';
HTMLscript TEXT DEFAULT '<p style="line-height: 14px; text- 
align: center; font-size: 12px; margin: 0;">';
rec_Product   RECORD;
cur_AppendProducts CURSOR(Customer_Email TEXT) 
FOR SELECT "Name", "Product","itemcount"
FROM dl."Products"
WHERE "email" = Customer_Email;
 BEGIN
 -- Open the cursor
  OPEN cur_Appendproducts(Customer_Email);
 LOOP
  -- fetch row into the film
  FETCH cur_Appendproducts INTO rec_Product;
 -- exit when no more row to fetch
  EXIT WHEN NOT FOUND; 
 -- build the output
  IF rec_Product.itemcount > 0 THEN 
     Products := HTMLscript || rec_Product."Product" || '</p>';
  END IF;
 END LOOP;
-- Close the cursor
CLOSE cur_Appendproducts;
RETURN Products;
END; $$ LANGUAGE plpgsql;

Is there some stipulation forcing you to use a cursor? This looks like it can be done with a FOR loop. It looks like you really want to RETURN SETOF text also. — J Spratt
– J Spratt, Commented Apr 22, 2019 at 18:17

J Spratt · Accepted Answer · 2019-04-23 04:16:52Z

2

I think another solution you can try without the use of CURSOR could be like the following:

CREATE OR REPLACE FUNCTION append_products(cust_email TEXT)
RETURNS SETOF TEXT
AS $$
DECLARE
    html_script TEXT DEFAULT '<p style="line-height: 14px; text-align: center; font-size: 12px; margin: 0;"> ';
    rec_product RECORD;
BEGIN
    FOR rec_product IN
        SELECT "Name", "Product", "itemcount"
        FROM dl."Products"
        WHERE "email" = cust_email
    LOOP
        IF rec_product.itemcount > 0 THEN
            RETURN NEXT html_script || rec_Product."Product" || ' <\p>';
        END IF;
    END LOOP;
END;
$$  LANGUAGE PLPGSQL;

You can check out an example DBFiddle here.

If you don't want a set returning function here is a single string return option:

CREATE OR REPLACE FUNCTION append_products(cust_email TEXT)
RETURNS TEXT
AS $$
DECLARE
    html_script TEXT DEFAULT '<p style="line-height: 14px; text-align: center; font-size: 12px; margin: 0;"> ';
    rec_product RECORD;
    result TEXT DEFAULT '';
BEGIN
    FOR rec_product IN
        SELECT "Product"
        FROM "Products"
        WHERE "email" = cust_email
    LOOP
        result := result || html_script || rec_Product."Product" || ' <\p>';
    END LOOP;

    RETURN result;
END;
$$  LANGUAGE PLPGSQL;

With a new fiddle here.

edited Apr 23, 2019 at 4:16

answered Apr 22, 2019 at 18:26

J Spratt

2,0521 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sandeep Over a year ago

Hi Spratt, I tested the results,but the results are duplicating,I want the results to be appended into a single string.For the results [email protected] there are two entries but the output resulted in 4 entries,I want it to be appended into a single row Do you have any suggestions about that? But thank a lot of for your reply.

J Spratt Over a year ago

I don't see how it could be duplicating can you provide sample input data? I've updated the answer for a non-set returning function.

Sandeep Over a year ago

Hey Spratt,Thanks a lot!! This is exactly i was looking for :-) many thanks man.

Collectives™ on Stack Overflow

Appending multiple row results in Postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related