Re: Trouble with NEW - Mailing list pgsql-general

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