Re: Trouble with NEW - Mailing list pgsql-general

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