Thread: Trouble with NEW
Hi
I would appreciate some fresh eyes on this expression -
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;
I receive an error ”record “new” has no field “fluid_id” “.
Bob
On 07/18/2012 12:07 PM, Bob Pawley wrote: > Hi > I would appreciate some fresh eyes on this expression - > 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; > I receive an error ”record “new” has no field “fluid_id” “. > Bob Are you in a trigger?
On Wednesday, July 18, 2012 11:07:34 AM Bob Pawley wrote: > Hi > > I would appreciate some fresh eyes on this expression - > > 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; > > I receive an error ”record “new” has no field “fluid_id” “. > Is that run within an insert or update trigger function? Does the table it's on have a field named fluid_id? > Bob -- When the Athenians finally wanted not to give to society but for society to give to them, when the freedom they wished for most was freedom from responsibility, then Athens ceased to be free and was never free again.” -- Edward Gibbon
It's an insert after trigger function. The table has a column named fluid_id. Bob -----Original Message----- From: Alan Hodgson Sent: Wednesday, July 18, 2012 11:15 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Trouble with NEW On Wednesday, July 18, 2012 11:07:34 AM Bob Pawley wrote: > Hi > > I would appreciate some fresh eyes on this expression - > > 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; > > I receive an error ”record “new” has no field “fluid_id” “. > Is that run within an insert or update trigger function? Does the table it's on have a field named fluid_id? > Bob -- When the Athenians finally wanted not to give to society but for society to give to them, when the freedom they wished for most was freedom from responsibility, then Athens ceased to be free and was never free again.” -- Edward Gibbon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 07/18/2012 12:28 PM, Bob Pawley wrote: > It's an insert after trigger function. > > The table has a column named fluid_id. Can we see the table schema. What I am looking for is quoted column name that would preserve case. > > Bob -- Adrian Klaver adrian.klaver@gmail.com
On Wednesday, July 18, 2012 12:28:00 PM Bob Pawley wrote: > It's an insert after trigger function. > > The table has a column named fluid_id. > > Bob Could you post the whole function? And a \d on the table?
When I substitute new.fluid_id for the actual fluid)id the expression returns the right value. Following is the table - CREATE TABLE p_id.fluids ( p_id_id integer, fluid_id serial, text_arrow geometry, line geometry, ip_op_equipment character varying(3), pipe_number character varying(100), pipe_size character varying, pipe_size_unit varchar (30), pipe_schedule varchar (30), dest_process varchar (30), dest_pump varchar (30), dest_pid varchar (30), source_process varchar (30), source_pump varchar (30), source_pid varchar (30), fluid_short character varying (10), fluid_name character varying(100), pump1 character varying(5), pump2 character varying(5), CONSTRAINT fluid_pk PRIMARY KEY (fluid_id) ); Bob -----Original Message----- From: Adrian Klaver Sent: Wednesday, July 18, 2012 1:07 PM To: Bob Pawley Cc: Alan Hodgson ; pgsql-general@postgresql.org Subject: Re: [GENERAL] Trouble with NEW On 07/18/2012 12:28 PM, Bob Pawley wrote: > It's an insert after trigger function. > > The table has a column named fluid_id. Can we see the table schema. What I am looking for is quoted column name that would preserve case. > > Bob -- Adrian Klaver adrian.klaver@gmail.com
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
On 19 Jul 2012, at 24:20, Bob Pawley wrote: > When I substitute new.fluid_id for the actual fluid)id the expression returns the right value. > > Following is the table - > > CREATE TABLE p_id.fluids > ( > p_id_id integer, > fluid_id serial, I think people meant the one on which the trigger fires ;) This one can't (shouldn't) be it, or you would introduce in an infinite loop by calling update on the same table. > On 07/18/2012 12:28 PM, Bob Pawley wrote: >> It's an insert after trigger function. >> >> The table has a column named fluid_id. > > Can we see the table schema. What I am looking for is quoted column name > that would preserve case. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
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
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
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
On 07/19/2012 08:41 AM, Bob Pawley wrote: > In all my reading of new and old I never made that connection. It makes more sense if you know what NEW and OLD represent. What follows is a simplification: 1)Postgres uses Multiversion Concurrency Control(MVCC). See here for brief intro: http://www.postgresql.org/docs/9.0/interactive/mvcc-intro.html 2) As a result on: A) INSERT. For each record you have only a corresponding NEW record that holds the values you are inserting. b) UPDATE. In Postgres an update is a two part process where you delete the old record and insert the new record. In that case you have both an OLD and a NEW record representing the respective values. c) DELETE. You are getting rid of a record and all you have is the OLD record representing the record you are deleting. 3) When you use a trigger or rule that uses the OLD, NEW records it only has access to those records for the table it is attached to. 4) If you want to pull information from another table, you either need to set up a FOREIGN KEY relationship that you can leverage or you need to do a query in the trigger function that pulls in the necessary information. > > Thanks Adrian > > Bob > -- Adrian Klaver adrian.klaver@gmail.com
> > 4) If you want to pull information from another table, you either need to set > up a FOREIGN KEY relationship that you can leverage or you need to do a > query in the trigger function that pulls in the necessary information. > I do not get where the "OR" comes from. There is nothing magical about FOREIGN KEY that causes NEW magically contain additionalvalues. Foreign Key or not you still have to "...do a query in the trigger function...". David J.
On 07/19/2012 11:26 AM, David Johnston wrote: >> >> 4) If you want to pull information from another table, you either need to set >> up a FOREIGN KEY relationship that you can leverage or you need to do a >> query in the trigger function that pulls in the necessary information. >> > > I do not get where the "OR" comes from. There is nothing magical about FOREIGN KEY that causes NEW magically contain additionalvalues. Foreign Key or not you still have to "...do a query in the trigger function...". I think there is:) If you have a FK between the table you have the trigger on a table you want to update you already have the NEW or OLD values in the trigger table with which to find the appropriate record in the other table. This covers the OP case. Without a FK you have to create your own relationship. > > David J. > > -- Adrian Klaver adrian.klaver@gmail.com