Hi, I've been staring at this for hours and was hoping somebody could point me in the right direction.
I have a trigger setup on a table to update some values based on the
values being inserted/updated and keep getting warning messages in the
logs, even tho this query has the desired effect and the values are
updated in the database:
2011-09-20 15:20:50 BST WARNING: here, 'email':3B
'j...@stride.me.uk':2B 'test':1A
2011-09-20 15:20:50 BST CONTEXT: SQL statement "UPDATE resource_field_values
SET
boolean_value=false,
updated=now(),
updated_by='221ee00f-df61-4095-a380-896b9947f551'
WHERE
boolean_value=true AND
resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND
key!=NEW.key AND
resource_key IN
(
SELECT DISTINCT r.key
FROM
resource_field_values e,
resource_field_values t,
resources r
WHERE
r.key=e.resource_key AND
r.key=t.resource_key AND
r.subsequent_version_key IS NULL AND
r.deleted=false AND
e.resource_key=t.resource_key AND
e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd' AND
t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91' AND
t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f'
--AND
--lower(trim(e.varchar_value)) = lower(trim(NEW.varchar_value))
)"
PL/pgSQL function "process_newsletter_email_address" line 5 at SQL
statement
I'm confused as line 5 is surely updating the uuid value for
updated_by. Any help/pointers would be much appreciated and I've
included the trigger that calls this is:
CREATE TRIGGER process_newsletter_email_uniqueness
BEFORE INSERT OR UPDATE ON
"e57550ed-06d9-46a8-be4f-bf8192d7ad5d".resource_field_values
FOR EACH ROW
WHEN (
NEW.resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND
NEW.boolean_value = true
)
EXECUTE PROCEDURE
"e57550ed-06d9-46a8-be4f-bf8192d7ad5d".process_newsletter_email_address();
And the function looks like:
CREATE OR REPLACE FUNCTION
"e57550ed-06d9-46a8-be4f-bf8192d7ad5d".process_newsletter_email_address()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF (TG_OP = 'UPDATE' OR
'TG_OP' = 'INSERT')
THEN
UPDATE
resource_field_values
SET
boolean_value=false,
updated=now(),
updated_by='221ee00f-df61-4095-a380-896b9947f551'
WHERE
boolean_value=true AND
resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2' AND
--resource_key!=NEW.resource_key AND
resource_key IN
(
SELECT r.key
FROM
resource_field_values e,
resource_field_values t,
resources r,
(
-- This gets the email of the value we are updating
SELECT e.varchar_value
FROM
resource_field_values e,
resource_field_values t,
resource_field_values n
WHERE
e.subsequent_version_key IS NULL AND
t.subsequent_version_key IS NULL AND
n.subsequent_version_key IS NULL AND
e.resource_key=t.resource_key AND
e.resource_key=n.resource_key AND
e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd'
AND
t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91'
AND
n.resource_type_field_key='6830cfe2-ba89-446e-8baf-da852050bff2'
AND
t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f'
AND
n.key=NEW.key
) n
WHERE
r.key=e.resource_key AND
r.key=t.resource_key AND
e.subsequent_version_key IS NULL AND
t.subsequent_version_key IS NULL AND
r.subsequent_version_key IS NULL AND
r.deleted=false AND
e.resource_key=t.resource_key AND
e.resource_type_field_key='b8310e43-6434-42d3-b13f-d38be4d3e5dd' AND
t.resource_type_field_key='f540b4c6-486d-4812-9155-30051e3d7e91' AND
t.option_value='d73bd962-cb61-4b94-8a55-f819615c623f' AND
lower(trim(e.varchar_value)) = lower(trim(n.varchar_value))
) ,;
END IF;
RETURN NEW;
END;
$$;
--
Jake Stride
Find out more http://about.me/jakestride or follow me on twitter @jake.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
