In all my reading of new and old I never made that connection.
Thanks Adrian
Bob
-----Original Message-----
From: Adrian Klaver
Sent: Thursday, July 19, 2012 6:50 AM
To: Bob Pawley
Cc: Alan Hodgson ; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Trouble with NEW
On 07/19/2012 06:43 AM, Bob Pawley wrote:
The function is too long to copy.
I separated it into another trigger function with just the update
statement.
Here is the error -
ERROR: record "new" has no field "fluid_id"
SQL state: 42703
Context: SQL statement "update p_id.fluids
set fluid_short =
(select shape.text
from shape, num_search
where (select st_within(shape.wkb_geometry,
st_geometryn(num_search.the_geom4, 1)) = 'true')
and text !~ '[0-9]')
---------------------------------------------------------
Here is the trigger function -
CREATE OR REPLACE FUNCTION fluid_name()
RETURNS trigger AS $$
begin
update p_id.fluids
set fluid_short =
(select shape.text
from shape, num_search
where (select st_within(shape.wkb_geometry,
st_geometryn(num_search.the_geom4, 1)) = 'true')
and text !~ '[0-9]')
where p_id.fluids.fluid_id = NEW.fluid_id;
RETURN NULL;
End;
$$ LANGUAGE plpgsql;
create trigger r_fluidname after insert on project.project
for each row execute procedure fluid_name();
--------------------------------------------
Here is the trigger script -
-- Trigger: r_fluidname on project.project
-- DROP TRIGGER r_fluidname ON project.project;
CREATE TRIGGER r_fluidname
AFTER INSERT
ON project.project
FOR EACH ROW
EXECUTE PROCEDURE public.fluid_name();
--------------------------------------------
Here is the function script -
-- Function: public.fluid_name()
-- DROP FUNCTION public.fluid_name();
CREATE OR REPLACE FUNCTION public.fluid_name()
RETURNS trigger AS
$BODY$
begin
update p_id.fluids
set fluid_short =
(select shape.text
from shape, num_search
where (select st_within(shape.wkb_geometry,
st_geometryn(num_search.the_geom4, 1)) = 'true')
and text !~ '[0-9]')
where p_id.fluids.fluid_id = NEW.fluid_id;
RETURN NULL;
End;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.fluid_name()
OWNER TO postgres;
------------------------------------
Here is the triggering table -
-- Table: project.project
-- DROP TABLE project.project;
CREATE TABLE project.project
(
p_id_id serial NOT NULL,
p_id_name character varying(75),
project_name character varying(75),
project_id integer,
number_of_processes integer,
p_id_number character varying(30),
CONSTRAINT project_pkey PRIMARY KEY (p_id_id ),
CONSTRAINT name_ UNIQUE (p_id_name )
)
WITH (
OIDS=FALSE
);
ALTER TABLE project.project
OWNER TO postgres;
-- Trigger: pidnum on project.project
-- DROP TRIGGER pidnum ON project.project;
Help would be appreciated.
There is no fluid_id in the project.project table. The trigger NEW
record only pulls from the table the trigger is attached to. You will
have to find some other way of relating the project table to the fluids
table.
Bob
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
