Re: [GENERAL] Trouble with NEW

[Bob Pawley]

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@postgresql.org 
Subject: Re: [GENERAL] 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.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