Thread: Where
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
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
Thank you - what is the correct command??? Bob ----- Original Message ----- From: "Uwe C. Schroeder" <uwe@oss4u.com> To: <pgsql-general@postgresql.org> Cc: "Bob Pawley" <rjpawley@shaw.ca> Sent: Thursday, November 10, 2005 3:34 PM Subject: Re: [GENERAL] 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
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@oss4u.com>
> To: <pgsql-general@postgresql.org>
> Cc: "Bob Pawley" <rjpawley@shaw.ca>
> Sent: Thursday, November 10, 2005 3:34 PM
> Subject: Re: [GENERAL] 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
By 'one record' do you mean one full row or one column of one row?? Bob ----- Original Message ----- From: "Uwe C. Schroeder" <uwe@oss4u.com> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: <pgsql-general@postgresql.org> Sent: Thursday, November 10, 2005 5:05 PM Subject: Re: [GENERAL] 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@oss4u.com> >> To: <pgsql-general@postgresql.org> >> Cc: "Bob Pawley" <rjpawley@shaw.ca> >> Sent: Thursday, November 10, 2005 3:34 PM >> Subject: Re: [GENERAL] 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
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
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@oss4u.com> > To: "Bob Pawley" <rjpawley@shaw.ca> > Cc: <pgsql-general@postgresql.org> > Sent: Thursday, November 10, 2005 5:05 PM > Subject: Re: [GENERAL] 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@oss4u.com> > >> To: <pgsql-general@postgresql.org> > >> Cc: "Bob Pawley" <rjpawley@shaw.ca> > >> Sent: Thursday, November 10, 2005 3:34 PM > >> Subject: Re: [GENERAL] 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
Thanks again. I'll give it a try. Bob ----- Original Message ----- From: "Uwe C. Schroeder" <uwe@oss4u.com> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: <pgsql-general@postgresql.org> Sent: Thursday, November 10, 2005 7:44 PM Subject: Re: [GENERAL] Where > 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@oss4u.com> >> To: "Bob Pawley" <rjpawley@shaw.ca> >> Cc: <pgsql-general@postgresql.org> >> Sent: Thursday, November 10, 2005 5:05 PM >> Subject: Re: [GENERAL] 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@oss4u.com> >> >> To: <pgsql-general@postgresql.org> >> >> Cc: "Bob Pawley" <rjpawley@shaw.ca> >> >> Sent: Thursday, November 10, 2005 3:34 PM >> >> Subject: Re: [GENERAL] 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
Uwe In attempting to understand the underlying intent of the statement, I have a question. What purpose does the statement "myrow" serve in this expression. Bob ----- Original Message ----- From: "Uwe C. Schroeder" <uwe@oss4u.com> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: <pgsql-general@postgresql.org> Sent: Thursday, November 10, 2005 5:05 PM Subject: Re: [GENERAL] 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@oss4u.com> >> To: <pgsql-general@postgresql.org> >> Cc: "Bob Pawley" <rjpawley@shaw.ca> >> Sent: Thursday, November 10, 2005 3:34 PM >> Subject: Re: [GENERAL] 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