Re: Trouble with NEW

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.

Bob

-----Original Message-----
From: Adrian Klaver
Sent: Wednesday, July 18, 2012 5:47 PM
To: Bob Pawley
Cc: Alan Hodgson ; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Trouble with NEW

On 07/18/2012 03:20 PM, Bob Pawley wrote:
> When I substitute new.fluid_id for the actual fluid)id the expression
> returns the right value.

Huh? I thought that was what was causing the problem. From your original
post:

"
where p_id.fluids.fluid_id = NEW.fluid_id;

I receive an error ”record “new” has no field “fluid_id” “.

"
Per previous suggestion can we see?:

1)The trigger definition

2) The function.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com