Re: [GENERAL] Trouble with NEW

[Bob Pawley]

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@postgresql.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.kla...@gmail.com 


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general