one full row - NOT two or more rows. On Thursday 10 November 2005 17:23, Bob Pawley wrote: > By 'one record' do you mean one full row or one column of one row?? > > Bob > > ----- Original Message ----- > From: "Uwe C. Schroeder" <uwe@xxxxxxxxx> > To: "Bob Pawley" <rjpawley@xxxxxxx> > Cc: <pgsql-general@xxxxxxxxxxxxxx> > Sent: Thursday, November 10, 2005 5:05 PM > Subject: Re: Where > > > This will work if you can guarantee that it's only one record > > > > INSERT INTO pipe (fluid_id,contain) SELECT (fluid_id,contain) FROM > > process WHERE contain='ip' > > > > otherwise (more than one record) you have to loop over the resultset, > > something like (off the top of my head) > > > > create or replace function base() returns trigger as $$ > > DECLARE > > myrow RECORD; > > BEGIN > > insert into pipe (fluid_id) values (new.fluid_id); > > for myrow in select * from process where contain = 'ip' loop > > insert into pipe(fluid_id,contain) values > > (row.fluid_id,row.contain); > > if not found then > > raise exception 'error creating record'; > > end if; > > end loop; > > return NULL; > > END; > > > > On Thursday 10 November 2005 15:56, Bob Pawley wrote: > >> Thank you - what is the correct command??? > >> > >> Bob > >> > >> ----- Original Message ----- > >> From: "Uwe C. Schroeder" <uwe@xxxxxxxxx> > >> To: <pgsql-general@xxxxxxxxxxxxxx> > >> Cc: "Bob Pawley" <rjpawley@xxxxxxx> > >> Sent: Thursday, November 10, 2005 3:34 PM > >> Subject: Re: Where > >> > >> > SELECT INTO xxxx > >> > tries to create table xxxx > >> > See: > >> > http://www.postgresql.org/docs/8.0/interactive/sql-selectinto.html > >> > > >> > Why do you do the select into anyways? It does nothing. > >> > If you try to update table pipe with the select result you have the > >> > wrong > >> > command. > >> > > >> > UC > >> > > >> > On Thursday 10 November 2005 14:24, Bob Pawley wrote: > >> >> I am attempting to transfer the data in the fluid_id column of table > >> >> process into column fluid_id of table pipe. > >> >> > >> >> This should happen only when column contain of table process holds > >> >> the value 'ip'. > >> >> > >> >> Here is the command that I am having trouble with. > >> >> ------- > >> >> create table process (fluid_id integer primary key, process varchar, > >> >> contain varchar); > >> >> > >> >> create table pipe ( fluid_id integer not null, contain varchar); > >> >> > >> >> > >> >> > >> >> create or replace function base() returns trigger as $$ > >> >> > >> >> begin > >> >> > >> >> > >> >> > >> >> insert into pipe (fluid_id) values (new.fluid_id); > >> >> > >> >> select * into pipe from process where contain = 'ip'; > >> >> > >> >> > >> >> > >> >> return null; > >> >> > >> >> > >> >> > >> >> end; > >> >> > >> >> $$ language plpgsql; > >> >> > >> >> > >> >> > >> >> create trigger trig1 after insert on process > >> >> > >> >> > >> >> > >> >> for each row execute procedure base(); > >> >> > >> >> > >> >> > >> >> insert into process (fluid_id, process, contain) > >> >> > >> >> values ('2', 'water', 'ip'); > >> >> > >> >> ------------------- > >> >> On inserting data this error comes back - > >> >> ------- > >> >> ERROR: relation "pipe" already exists > >> >> CONTEXT: SQL statement "SELECT * INTO pipe from process where > >> >> contain > >> >> = > >> >> 'ip'" PL/pgSQL function "base" line 4 at SQL statement > >> >> ------ > >> >> Of course the table pipe does already exist - it is a permanent > >> >> table. > >> >> > >> >> Is the program looking for some other target?? Perhaps a temporary > >> >> table?? > >> >> > >> >> > >> >> > >> >> Or am I completely screwed up??? > >> >> > >> >> > >> >> > >> >> Bob > >> > > >> > -- > >> > UC > >> > > >> > -- > >> > Open Source Solutions 4U, LLC 2570 Fleetwood Drive > >> > Phone: +1 650 872 2425 San Bruno, CA 94066 > >> > Cell: +1 650 302 2405 United States > >> > Fax: +1 650 872 2417 > > > > -- > > UC > > > > -- > > Open Source Solutions 4U, LLC 2570 Fleetwood Drive > > Phone: +1 650 872 2425 San Bruno, CA 94066 > > Cell: +1 650 302 2405 United States > > Fax: +1 650 872 2417 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq -- UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
