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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

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