Le mercredi 07 mai 2008, Dimitri Fontaine a écrit : > Ok, I've been quite bad at explaining the case, let's retry.
Thanks a lot to the OP on #postgresqlfr (nickname renchap), who is providing attached test case, where you'll see how we hacked our way into information_schema to have the insert rule insert DEFAULT instead of NULL. Of course the OP loses here the option to error out on NULL input, but the application is being ported from MySQL so he's not losing any feature here. Regards, -- dim
gwow_dev_renchap=# select version();
version
--------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.6 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2)
(1 row)
gwow_dev_renchap=# select get_site_id();
get_site_id
-------------
1
(1 row)
gwow_dev_renchap=# \df+ get_default_value;
List of functions
Schema | Name | Result data type |
Argument data types | Owner | Language | Source code
| Description
--------+-------------------+-----------------------------------+---------------------------------------------------+---------+----------+-----------------------------------------------------------------------------------------------+-------------
public | get_default_value | information_schema.character_data | table
character varying, column character varying | renchap | sql | SELECT
column_default FROM information_schema.columns WHERE table_name=$1 AND
column_name=$2; |
(1 row)
gwow_dev_renchap=# \d zf_categories
Table "public.zf_categories"
Column | Type | Modifiers
---------------+-----------------------+------------------------------------------------------------
id | integer | not null default
nextval('zf_categories_id_seq'::regclass)
cat_name | character varying(80) | not null default 'New
Category'::character varying
disp_position | integer | not null default 0
site_id | integer |
Indexes:
"zf_categories_pkey" PRIMARY KEY, btree (id)
"zf_categories_site_id" btree (site_id)
"zf_categories_site_id_idx" btree (site_id)
gwow_dev_renchap=# \d z_categories
View "public.z_categories"
Column | Type | Modifiers
---------------+-----------------------+-----------
id | integer |
cat_name | character varying(80) |
disp_position | integer |
View definition:
SELECT zf_categories.id, zf_categories.cat_name, zf_categories.disp_position
FROM zf_categories
WHERE zf_categories.site_id = get_site_id();
Rules:
delete_z_categories AS
ON DELETE TO z_categories DO INSTEAD DELETE FROM zf_categories
WHERE zf_categories.id = old.id AND zf_categories.site_id = get_site_id()
insert_z_categories AS
ON INSERT TO z_categories DO INSTEAD INSERT INTO zf_categories (site_id,
cat_name, disp_position)
VALUES (get_site_id(), COALESCE(new.cat_name,
get_default_value('zf_categories'::character varying, 'cat_name'::character
varying)::character varying), COALESCE(new.disp_position,
get_default_value('zf_categories'::character varying,
'disp_position'::character varying)::integer))
update_z_categories AS
ON UPDATE TO z_categories DO INSTEAD UPDATE zf_categories SET id = new.id,
cat_name = new.cat_name, disp_position = new.disp_position
WHERE zf_categories.id = old.id AND zf_categories.site_id = get_site_id()
gwow_dev_renchap=# SELECT * FROM zf_categories ORDER BY id;
id | cat_name | disp_position | site_id
----+---------------+---------------+---------
1 | Test category | 1 | 2
2 | new cat 3 | 0 | 2
(2 rows)
gwow_dev_renchap=# INSERT INTO z_categories (cat_name, disp_position) VALUES
('My Cat', 5);
INSERT 0 1
gwow_dev_renchap=# SELECT * FROM zf_categories ORDER BY id;
id | cat_name | disp_position | site_id
----+---------------+---------------+---------
1 | Test category | 1 | 2
2 | new cat 3 | 0 | 2
22 | My Cat | 5 | 1
(3 rows)
gwow_dev_renchap=# INSERT INTO z_categories (cat_name) VALUES ('My New Cat');
INSERT 0 1
gwow_dev_renchap=# SELECT * FROM zf_categories ORDER BY id;
id | cat_name | disp_position | site_id
----+---------------+---------------+---------
1 | Test category | 1 | 2
2 | new cat 3 | 0 | 2
22 | My Cat | 5 | 1
23 | My New Cat | 0 | 1
(4 rows)
gwow_dev_renchap=# UPDATE z_categories SET disp_position=2;
UPDATE 2
gwow_dev_renchap=# SELECT * FROM zf_categories ORDER BY id;
id | cat_name | disp_position | site_id
----+---------------+---------------+---------
1 | Test category | 1 | 2
2 | new cat 3 | 0 | 2
22 | My Cat | 2 | 1
23 | My New Cat | 2 | 1
(4 rows)
gwow_dev_renchap=# UPDATE z_categories SET disp_position=2 WHERE id=1;
UPDATE 0
gwow_dev_renchap=# SELECT * FROM z_categories ORDER BY id;
id | cat_name | disp_position
----+------------+---------------
22 | My Cat | 2
23 | My New Cat | 2
(2 rows)
gwow_dev_renchap=# DELETE FROM z_categories WHERE id = 23;
DELETE 1
gwow_dev_renchap=# SELECT * FROM zf_categories ORDER BY id;
id | cat_name | disp_position | site_id
----+---------------+---------------+---------
1 | Test category | 1 | 2
2 | new cat 3 | 0 | 2
22 | My Cat | 2 | 1
(3 rows)
gwow_dev_renchap=# DELETE FROM z_categories;
DELETE 1
gwow_dev_renchap=# SELECT * FROM zf_categories ORDER BY id;
id | cat_name | disp_position | site_id
----+---------------+---------------+---------
1 | Test category | 1 | 2
2 | new cat 3 | 0 | 2
(2 rows)
signature.asc
Description: This is a digitally signed message part.
