Trigger/Query Warnings

[Jake Stride <jake@xxxxxxxxxxxx>]

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
'jake@xxxxxxxxxxxx':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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general