tag:blogger.com,1999:blog-3346090548501966296.post1210694095606456557..comments2024-03-26T05:03:04.875-07:00Comments on Perspectives on LedgerSMB: PostgreSQL OR Modelling Part 2: Intro to Object Relational Classes in PostgreSQLChris Travershttp://www.blogger.com/profile/06211762965865744803noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-3346090548501966296.post-51042746337091391512012-09-12T05:15:52.634-07:002012-09-12T05:15:52.634-07:00Hi, Chris
Where is the definition of in_item in ...Hi, Chris<br /><br />Where is the definition of in_item in <br /><br /><br /> CREATE FUNCTION save(inventory_item)<br />RETURNS inventory_item<br />LANGUAGE PLPGSQL STABLE AS<br />$$<br /> DECLARE out_item inventory_item;<br /><br /> BEGIN<br /> -- we don't want to allow accounts to change on existing items<br /> UPDATE inventory_item<br /> SET sku = in_item.sku,<br /> description = in_item.description,<br /> last_cost = in_item.last_cost,<br /> sell_price = in_item.sell_price,<br /> active = in_item.active<br /> WHERE id = in_item.id;<br /><br /> IF FOUND THEN<br /> SELECT * INTO out_item FROM inventory_item<br /> WHERE id = in_item.id;<br /> RETURN out_item;<br /> ELSE<br /> INSERT INTO inventory_item<br /> (cogs_account_id,<br /> inv_account_id,<br /> income_account_id,<br /> sku,<br /> description,<br /> last_cost,<br /> sell_price,<br /> active)<br /><br /> VALUES (in_item.cogs_account_id,<br /> in_item.inv_account_id,<br /> in_item.income_account_id,<br /> in_item.sku,<br /> in_item.description,<br /> in_item.last_cost,<br /> in_item.sell_price,<br /> in_item.active);<br /> SELECT * INTO out_item<br /> FROM inventory_item<br /> WHERE id = currval('inventory_item_id_seq');<br /> RETURN out_item;<br /><br /> END IF;<br /><br /> END;<br />$$;Anonymoushttps://www.blogger.com/profile/09334296323234709030noreply@blogger.comtag:blogger.com,1999:blog-3346090548501966296.post-44589585334054854692012-09-12T05:07:47.086-07:002012-09-12T05:07:47.086-07:00INSERT INTO account (id, accno, description)
VALUE...INSERT INTO account (id, accno, description)<br />VALUES (1, '1500', 'Inventory'),<br /> (2, '4500', 'Sales'),<br /> (3, '5500', 'Purchase'); <br /><br /><br />Should accno be control_code, since it's control_code in CREATE TABLE account (<br /> id int not null unique,<br /> control_code text primary key, -- account number<br /> description text not null<br />); Anonymoushttps://www.blogger.com/profile/09334296323234709030noreply@blogger.comtag:blogger.com,1999:blog-3346090548501966296.post-22926665084120147612012-08-25T21:19:01.264-07:002012-08-25T21:19:01.264-07:00Note this sort of approach is what makes table inh...Note this sort of approach is what makes table inheritance useful. In the next week we will be exploring how to use table inheritance without destroying relational integrity.Chris Travershttps://www.blogger.com/profile/06211762965865744803noreply@blogger.comtag:blogger.com,1999:blog-3346090548501966296.post-21230135535854025102012-08-25T07:54:46.559-07:002012-08-25T07:54:46.559-07:00Thanks! I will correct this!Thanks! I will correct this!Chris Travershttps://www.blogger.com/profile/06211762965865744803noreply@blogger.comtag:blogger.com,1999:blog-3346090548501966296.post-44890837226995272782012-08-25T04:58:50.290-07:002012-08-25T04:58:50.290-07:00> If we find ourselves doing a lot of such quer...> If we find ourselves doing a lot of such queries and need to index the values we can create an index:<br />><br />> CREATE INDEX inventory_item_markup_idx <br />> ON inventory_item (markup(inventory_item));<br />><br />> Note this statement does not support object.method notation here. You must do method(class) instead.<br /><br />It works as<br /><br />CREATE INDEX inventory_item_markup_idx ON inventory_item ((inventory_item.markup));<br /><br />with the extra parentheses. Normally an expression (as opposed to a plain column reference) in an index definition has to have extra parentheses round it, but there's an exception for function calls which is why your original version works.Unknownhttps://www.blogger.com/profile/09600019690045008534noreply@blogger.com