[Newbie] Postgresql for Odoo/Python: Concatenate rows value until condition = true? - Mailing list pgsql-novice

Hi all,
is just some days I'm starting to use postgreSQL and Python massively
and I really need time to learn them well. Even if I have a background
as maintainer in linux and SAP (ABAP) I'm facing various difficulties
start learning again. After all I'm an over 40 so please don't blame
me for that...

I'm creating a new view for a customized Point of Sale module in Odoo
(formerly OpenERP) for my restaurant. It's a specific view for the
kitchen, already filtered to show just kitchen's related items of a
PoS order.
Now I would like to show a particular series of product with the name
starting with "#" in a field populated by a function side by side with
the "standard" products. In fact I'm speaking of a set of instructions
coded as products to be available on PoS and to suit my needs I
decided to create kitchen instructions as a product (service). In my
PoS there are 3 categories that were created like products but are in
fact instructions (ADD, SUBTRACT, ADDITIONAL NOTE).

Problem is Odoo uses massively XML, Python and Qweb other than be
based on PostgreSQL. I worked from time to time with SQL and with
reports engine (Crystal Reports and others) but I really need help to
archive the result here because I'm editing the DB not just viewing
it.
I'm not asking to write code from me, or at least is not the primary
scope, but since I would like to understand what I'm doing some
explained examples will be really appreciated.

A typical, filtered, order on DB will show as:
Code:

id (integer); order id (integer); name (char); x_notes(char)
1922;716;"Pom. Secchi";""
1923;716;"# + Alface";""
1924;716;"# - Rucola";""
1925;716;"# ---- servir depois ----";""
1926;716;"Nutella banana";""
1927;716;"# + Creme de ricota";""

Please note the field x_notes is empty. What I would like to archive
is a view like:

Code:

Name (char); x_notes(char)
Pom. Secchi;# + Búfala # - Mussarela
Nutella banana;# + Mussarela

Please, let me explain: I need to select just the strings starting
with the "#" (my script-wake character) between two normal code and
this is the hard thing to do. I don't need to delete the values I
"move" because I need the stock view when I print the receipt o the
thermal printers. I know this will let grow the db with redundant data
but we are talking about a small db.

The flow I would like to see with the needed, real, fields should be
more less the follow:

SELECT pos_order_line.id, pos_order_line.order_id,
product_template.name, pos_order_line.x_notes
FROM public.pos_order_line, public.product_template
WHERE pos_order_line.product_id = product_template.id AND
pos_order_line.order_id = 716 # This must be a variable generated from
Odoo (Python)
ORDER BY pos_order_line.id DESC

WHILE pos_order_line.order_id IS THE SAME:
DO a LOOP:
IF product_template.name ILIKE '#%'
THEN ADD without deleting the VALUE of product_template.name TO
product_template.name OF the previous row number (pos_order_line.id =
pos_order_line.id - 1) and RESTART
ELSE (pos_order_line.id = pos_order_line.id - 1) and RESTART

I will call this function using python (and psycopg2 on postgresql
9.4) with on_change or extending the workflow when an order is marked
as "invoiced" but should be good have a stored function directly on DB
is possible.
I'm already familiar on how call a python procedure on odoo then this
is not the scope of this request.
That's complicated for a newbie, I know, but I'm ready to be
criticized, insulted and so on... Have mercy if you can!

Thanks!

Federico