[SQL] Implementation of a bag pattern using rules
Hello,
I've been playing around with a simple solution for a bag or sparse
matrix using rules,
but have encountered a few problems I wish to discuss.
The bag pattern is commonly used for shopping baskets (item => quantity).
This sollution can also be used for a sparse matrix too (row,col => value).
Example:
CREATE TABLE bag_test
(
item text PRIMARY KEY,
qty integer
);
To add/modify/del items in the above table is tedious,
you need to first check for existence of an item then choose your SQL
statement (INSERT/UPDATE/DELETE/do nothing).
I want to be able to add/modify/del an item using only INSERT.
eg:
INSERT INTO bag_test VALUES ('apple', 1);
INSERT INTO bag_test VALUES ('apple', 12);
In the second statement, ee have a choice though, of whether to
increase the quantity of 'apple' by 12, or set the quantity of 'apple'
to 12.
So, for the absolute option (set 'apple' to 12), we can use the
following rule:
CREATE RULE bag_abs AS ON INSERT TO bag_test
WHERE
EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item)
DO INSTEAD
UPDATE bag_test SET qty = NEW.qty WHERE item = NEW.item;
I also want the item to be deleted if it's quantity is <= 0:
CREATE RULE bag_del AS ON UPDATE TO bag_test
WHERE
NEW.qty <= 0
DO INSTEAD
DELETE FROM bag_test WHERE item = NEW.item;
Alternatively, for the relative option (increase 'apple' by 12), replace
the 'bag_abs' rule with:
CREATE RULE bag_rel AS ON INSERT TO bag_test
WHERE
EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item)
DO INSTEAD
UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item;
(You still need the 'bag_del' rule if you want quantity <= 0 to be deleted)
Unfortunately there is a problem with 'bag_rel':
When the item already exists, it works fine, the item's quantity
is increased by the amount given in the INSERT statement.
BUT, if the item doesn't exist it gets double the quantity given in the
statement.
eg:
> SELECT * FROM bag_test;
item | qty
--+-
(0 rows)
> INSERT INTO bag_test VALUES ('apple', 12);
INSERT 0 1
> SELECT * FROM bag_test;
item | qty
---+-
apple | 24
(1 row)
This is double the expected value!
> INSERT INTO bag_test VALUES ('apple', 12);
INSERT 0 0
> SELECT * FROM bag_test;
item | qty
---+-
apple | 36
(1 row)
But, this worked fine (increased by 12)!
> INSERT INTO bag_test VALUES ('apple', -36);
INSERT 0 0
> SELECT * FROM bag_test;
item | qty
--+-
(0 rows)
Deleting works fine too.
Does anyone know how to prevent the problem with the initial insert?
I've read 'The Rule System' chapter several times, it's fairly heavy going,
and results in much head scratching, but I still can't work out how to
fix it.
Any suggestions on improving the rules?
Other than the problem mentioned, can anyone see a flaw in this method?
Cheers
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Implementation of a bag pattern using rules
Robert Creager wrote: When grilled further on (Mon, 09 Feb 2004 12:42:10 +), Mark Gibson <[EMAIL PROTECTED]> confessed: CREATE RULE bag_abs AS ON INSERT TO bag_test WHERE EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) DO INSTEAD UPDATE bag_test SET qty = NEW.qty WHERE item = NEW.item; CREATE RULE bag_rel AS ON INSERT TO bag_test WHERE EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) DO INSTEAD UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item; I'm no expert, just up early. I believe both of these rules are tripping. bag_abs is likely going first, then bag_rel, so bag_abs is inserting the record, then bag_rel is updating it. You could verify this by deleting the two rules, then re-creating in the opposite order, and see if your inserted values change. How would you expect the system to choose one of the two rules, which is what you apparently expect? I probably didn't make this clear enough: The system doesn't choose, YOU choose EITHER 'bag_abs' OR 'bag_rel' depending on which behaviour is most appropriate for your application. 'bag_del' can be used in combination with either, to remove empty items. The 'bag_abs'/'bag_del' rules work perfectly - I've provided them for feedback, and hopefully others will find them useful. It's only the 'bag_rel' rule that is giving me a headache. Also, IIRC, rules are applied in alphabetical order, NOT the order in which they were created. Cheers. -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Implementation of a bag pattern using rules
Richard Sydney-Smith wrote:
Mark,
love the idea, guess I should have read it somewhere but haven't. Obvious
and beautiful. Please let me know if you or someone else solves the initial
double value.
I used to use functions for this kind of thing,
and was thinking that what SQL really needed was an 'UPDATE OR INSERT'
command,
then it suddenly came to me last night, it could be done with rules or
triggers.
[I've posted a trigger solution for the relative values separately, in
response to Tom Lanes help]
Got me thinking of all the places I cold have used this instead of coding
select/insert/update/delete.
Also have you worked a solutions where both the abs and relative inserts
apply to the same bag
eg insert another apple vs set apples to 5
Hmmm, yeah, I'm wondering about that one. It would be handy.
Custom datatype maybe - an integer with a flag to indicate absolute or
relative???
eg:
INSERT INTO bag_test VALUES ('orange', '10 abs');
INSERT INTO bag_test VALUES ('orange', '-5 rel');
or views that modify an underlying table???
eg:
INSERT INTO bag_test_abs VALUES ('orange', 10);
INSERT INTO bag_test_rel VALUES ('orange', -5);
I have no idea yet whether these are possible though, any ideas?
Much of my attitude to triggers has been non-committal. Your example changes
that.
Triggers, rules and functions ROCK. It's allowed us to move all the
business logic into the
database itself so we can create really simple clients easily in any
language/environment.
Right, I'm off home now :)
Cheers
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Implementation of a bag pattern using rules
Tom Lane wrote: Mark Gibson <[EMAIL PROTECTED]> writes: CREATE RULE bag_rel AS ON INSERT TO bag_test WHERE EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item) DO INSTEAD UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item; This can't work because an ON INSERT rule fires after the INSERT itself is executed. I suspected that it may be impossible with rules, but I thought I'd ask, I'm still trying to get to grips with them. I think you need to use a BEFORE INSERT trigger instead. You could also extend the trigger to handle the delete-upon-reaching-zero logic. So, here's my proof-of-concept trigger for the relative quantities: CREATE OR REPLACE FUNCTION bag_rel_trigger() RETURNS TRIGGER AS ' DECLARE oldqty bag_test.qty%TYPE; BEGIN IF NEW.qty <> 0 THEN SELECT INTO oldqty qty FROM bag_test WHERE item = NEW.item; IF NOT FOUND AND NEW.qty > 0 THEN RETURN NEW; END IF; IF oldqty + NEW.qty <= 0 THEN DELETE FROM bag_test WHERE item = NEW.item; ELSE UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item; END IF; END IF; RETURN NULL; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER bag_rel BEFORE INSERT ON bag_test FOR EACH ROW EXECUTE PROCEDURE bag_rel_trigger(); I think it should be possible to make the trigger generic for any table, the quantity column could be passed as a parameter to the trigger, but it would require some horribly complex code to determine the primary key and lots of EXECUTE calls - a lot of overhead each time the trigger is called :( I was thinking maybe of a function thats create a trigger optimized for the table. Any ideas? Cheers -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Tip: a function for creating a remote view using dblink
Hello,
I'm posting a function here in the hope others may find it useful
and/or correct my mistakes/make improvements :)
This creates a view of a remote table, using dblink:
CREATE OR REPLACE FUNCTION dblink_create_view(text, text, text)
RETURNS VOID
LANGUAGE plpgsql
STRICT
AS '
DECLARE
connstr ALIAS FOR $1;
remote_name ALIAS FOR $2;
local_name ALIAS FOR $3;
schema_name text;
table_name text;
rec RECORD;
col_names text := '''';
col_defstext := '''';
sql_str text;
BEGIN
schema_name := split_part(remote_name, ''.'', 1);
table_name := split_part(remote_name, ''.'', 2);
FOR rec IN
SELECT * FROM dblink(connstr,
''SELECT
a.attname,
format_type(a.atttypid, a.atttypmod)
FROM
pg_catalog.pg_class c INNER JOIN
pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) INNER JOIN
pg_catalog.pg_attribute a ON (a.attrelid = c.oid)
WHERE
n.nspname = '' || quote_literal(schema_name) || '' AND
c.relname = '' || quote_literal(table_name) || '' AND
a.attisdropped = false AND
a.attnum > 0'')
AS rel (n name, t text)
LOOP
col_names := col_names || quote_ident(rec.n) || '','';
col_defs := col_defs || quote_ident(rec.n) || '' '' || rec.t || '','';
END LOOP;
sql_str := ''CREATE VIEW '' || local_name ||
'' AS SELECT * FROM dblink('' || quote_literal(connstr) || '','' ||
quote_literal(''SELECT '' || trim(trailing '','' from col_names) ||
'' FROM '' || quote_ident(schema_name) || ''.'' ||
quote_ident(table_name)) ||
'') AS rel ('' || trim(trailing '','' from col_defs) || '')'';
EXECUTE sql_str;
RETURN;
END
';
Usage example:
SELECT dblink_create_view('host=... dbname=... user=...',
'schema.remote_table', 'local_view');
SELECT * FROM local_view;
The schema MUST be specified for the remote table name.
Suggestions for improvement welcome. Any ideas?
Is there any existing site (a wiki for example) for posting PostgreSQL
specific tips?
(Wasn't sure if pgsql-sql is the right place for this kind of thing)
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] [HACKERS] Materialized View Summary
Jonathan M. Gardner wrote: You can view my summary at http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Comments and suggestions are definitely welcome. Fantastic, I was planning on a bit of materialized view investigations myself when time permits, I'm pleased to see you've started the ball rolling. I was thinking about your problem with mutable functions used in a materialized view. How about eliminating the mutable functions as much as possible from the underlying view definition, and create another view on top of the materialized view that has the mutable bits! Giving you the best of both worlds. I haven't tried this or thought it through very much - too busy - but I'd thought I'd throw it in for a bit o' head scratching, and chin stroking :) Cheers -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Elegant way to monitor for changes in a trigger and migrate
David B wrote: Folks, Perhaps you can helphell I'm sure you can! I want to monitor for changes in a table and migrate the OLD. record to audit table. Is there an elegant or generic way to do this so I can use across multiple tables with little change. You can use a rule to do this: CREATE RULE cust_audit AS ON UPDATE OR DELETE TO cust DO INSERT INTO cust_hist SELECT OLD.*; cust_hist should be identical to cust without a primary key or any constraints/foreign keys etc. I'm currently working on an auditing system at present, and will be releasing it soon if anyone is interested. It needs some cleaning up first, when I have time. -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] problems using phpPgAmin
beyaNet wrote: Hi, to those of you that may be using the above named admin tool, any ideas why I am unable to login with the postgres username even though I have amended the pg_hb file? Are there any other admin tools out there that i could use on a a unix box? Have you restarted PostgreSQL? Have you copied 'conf/config.inc.php-dist' to 'conf/config.inc.php' and configured it? BTW, there is a mailing list for phpPgAdmin at: [EMAIL PROTECTED] -- Mark Gibson Web Developer & Database Admin Cromwell Tools Ltd. Leicester, England. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Aggregate Function with Argument
David Siegal wrote:
I would like to create an aggregate function that returns a concatenation
of grouped values. It would be particularly useful if I could pass an
optional delimiter into the aggregate function.
I've managed to do this in two stages:
1. Collect the set of values into an array.
This can be done using a custom aggregate function, array_accum,
which is demonstrated within the PostgreSQL manual:
http://www.postgresql.org/docs/7.4/interactive/xaggr.html
But here it is again:
CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
It makes me wonder why this isn't a built-in aggregate???
2. Convert the array to a string.
Using the built-in function array_to_string:
http://www.postgresql.org/docs/7.4/interactive/functions-array.html
Example:
SELECT
team_number,
array_to_string(array_accum(member_name), ', ') AS members
FROM team
GROUP BY team_number;
You can also go full round-trip (delimited string -> set) using the
builtin function: string_to_array, and a custom pl/pgSQL function:
CREATE FUNCTION array_enum(anyarray) RETURNS SETOF anyelement AS '
DECLARE
array_a ALIAS FOR $1;
subscript_v integer;
BEGIN
FOR subscript_v IN array_lower(array_a,1) .. array_upper(array_a,1)
LOOP
RETURN NEXT array_a[subscript_v];
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql'
STRICT IMMUTABLE;
Example:
SELECT * FROM array_enum(string_to_array('one,two,three',','));
--
Mark Gibson
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
