I have a table 'pages' with a foreign key constraint on another table,
'photo'. The idea is that one should not be able to insert a
non-existant photo into a page. If I try to do this from the pgsql
prompt the insert fails as it should. However if I run a function, one
is able to add a row with an arbitrary n_photo_id (I discovered this
after I added this test arbitrarily to my unit test regime). The
relevant snippet of the function is below, together with the table
definitions.
Thanks for any help,
Rory
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION
fn_p2_edit_page (integer, integer, integer, integer, integer,
integer, varchar, varchar, integer) RETURNS INTEGER
AS '
DECLARE
userid ALIAS for $1;
pageid ALIAS for $2;
styleid ALIAS for $3;
photoid ALIAS for $4;
soundid ALIAS for $5;
pageno ALIAS for $6;
titletext ALIAS for $7;
storytext ALIAS for $8;
pagelength ALIAS for $9;
recone RECORD;
newpageival INTERVAL;
newpagetime TIME := ''00:00:00'';
newphotoval INTEGER := NULL;
newsoundval INTEGER := NULL;
newpageno INTEGER := 0;
BEGIN
...
IF photoid = -1 THEN
newphotoval := NULL;
ELSE
newphotoval := photoid;
END IF;
UPDATE
pages
SET
n_id_photo = newphotoval
WHERE
n_id = pageid;
IF NOT FOUND THEN
RAISE EXCEPTION ''Could not update photo : ref p2'';
RETURN 0;
END IF;
...
RETURN 1;
END;'
LANGUAGE plpgsql;
---------------------------------------------------------------------
Table "public.pages"
Column | Type | Modifiers
---------------+-----------------------------+---------------------------------------------------------
n_id | integer | not null default nextval('public.pages_n_id_seq'::text)
dt_created | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
dt_modified | timestamp without time zone | default ('now'::text)::timestamp(6) with time zone
t_title | character varying(100) |
t_text | text |
n_story | integer | not null
n_style | smallint | default 1
n_id_photo | integer |
n_id_sound | integer |
ti_length | time without time zone | default '00:00:06'
n_page_number | smallint | default 1
b_prototype | boolean | default false
Indexes: pages_pkey primary key btree (n_id)
Foreign Key constraints: $1 FOREIGN KEY (n_story) REFERENCES stories(n_id) ON UPDATE CASCADE ON DELETE CASCADE,
$2 FOREIGN KEY (n_id_photo) REFERENCES photo(n_id) ON UPDATE CASCADE ON DELETE SET NULL,
$3 FOREIGN KEY (n_id_sound) REFERENCES sound(n_id) ON UPDATE CASCADE ON DELETE SET NULL
Triggers: tr_update_modified_time,
tr_update_modified_time_sp
Table "public.photo"
Column | Type | Modifiers
---------------+--------------+---------------------------------------------------------
n_id | integer | not null default nextval('public.photo_n_id_seq'::text)
n_width | smallint |
n_height | smallint |
c_orientation | character(1) |
data | bytea |
label | character(2) |
Indexes: photo_pkey primary key btree (n_id)
Here is a row from pages with an invalide n_id_sound:
n_id | dt_created | dt_modified | t_title | t_text | n_story | n_style | n_id_photo |
n_id_sound| ti_length | n_page_number | b_prototype
------+----------------------------+----------------------------+---------+---------+---------+---------+------------+------------+-----------+---------------+-------------
6 | 2004-01-06 17:35:07.662787 | 2004-01-06 17:35:07.751908 | Rubbish | Rubbish | 3 | 1 | 1 |
99999 | 00:00:06 | 1 | f
(1 row)
--
Rory Campbell-Lange
<rory@campbell-lange.net>
<www.campbell-lange.net>
