--nextPart35613562.O9KhDehqfm
Content-Type: multipart/mixed;
boundary="Boundary-01=_iDXII/EO+IscYY9"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
--Boundary-01=_iDXII/EO+IscYY9
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Le mercredi 07 mai 2008, Dimitri Fontaine a =E9crit=A0:
> 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
providin=
g=20
attached test case, where you'll see how we hacked our way into=20
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=
=20
application is being ****ted from MySQL so he's not losing any feature
here.
Regards,
=2D-=20
dim
--Boundary-01=_iDXII/EO+IscYY9
Content-Type: text/plain;
charset="iso-8859-1";
name="renchap.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="renchap.txt"
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)
--Boundary-01=_iDXII/EO+IscYY9--
--nextPart35613562.O9KhDehqfm
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQBIIXDllBXRlnbh1bkRAp/aAJ0aqpt5Hb6gBLe+P1OzpEbAfgZQOwCeJ4cM
7Ux//9j0Vr2EhEkBKDScPy0=
=EVf8
-----END PGP SIGNATURE-----
--nextPart35613562.O9KhDehqfm--
|
