Is it databases in general, SQL or Postgresql?
Hope someone can help me learn.
Sample 1 below, does work. It transfers every serial number generated by table pr into table pi with no duplication.
Sample 2 below, does not work. From a logical (perhaps naive) extension of Sample 1, I adapted the function to identify which of the serial numbers in table pr is to be transferred to table pi. I am attempting to do this as part of the database structure not as data retrieval.
Could someone explain to me why this isn't acceptable as a simple basic function?
Could someone explain to me what needs to be changed, enhanced or modified to make this database structure work?
Thanks in advance.
Bob
Sample 1
create table pr
(Process_Name varchar (60) not null, Fluid_ID serial, Fluid varchar (30) not null, contain varchar (3),
constraint pr_pk primary key (fluid_id));
create table pi
(process_name varchar (60), Fluid_ID int4 not null, Fluid varchar (30),
contain varchar (3),
constraint pi_pk primary key (fluid_id),
constraint pi_fluid_id foreign key (fluid_id)
references pr (fluid_id) );
create or replace function base() returns trigger as $$
begin
insert into pi (fluid_id) values (new.fluid_id);
return null;
end;
$$ language plpgsql;
create trigger trig1 after insert on pr
for each row execute procedure base();
insert into pr (process_name, fluid, contain)
values ('boiler_water', 'water','ip');
---------------------------------
Sample 2
create table pr
(Process_Name varchar (60) not null, Fluid_ID serial, Fluid varchar (30) not null, contain varchar (3),
constraint pr_pk primary key (fluid_id));
create table pi
(process_name varchar (60), Fluid_ID int4 not null, Fluid varchar (30),
contain varchar (3),
constraint pi_pk primary key (fluid_id),
constraint pi_fluid_id foreign key (fluid_id)
references pr (fluid_id) );
create or replace function base() returns trigger as $$
begin
insert into pi (fluid_id) values (new.fluid_id)
where pr (contain) = 'ip';
return null;
end;
$$ language plpgsql;
create trigger trig1 after insert on pr
for each row execute procedure base();
insert into pr (process_name, fluid, contain)
values ('boiler_water', 'water','ip');
Error Message -
ERROR: syntax error at or near "where" at character 41
QUERY: insert into pi (fluid_id) values ( $1 ) where pr (contain) = 'ip'
CONTEXT: PL/pgSQL function "base" line 2 at SQL statement
Bob Pawley wrote:
Sample 2 below, does not work. From a logical (perhaps naive) extension
of Sample 1, I adapted the function to identify which of the serial
numbers in table pr is to be transferred to table pi. I am attempting to
do this as part of the database structure _not_ as data retrieval.Could someone explain to me why this isn't acceptable as a simple basic
function?Could someone explain to me what needs to be changed, enhanced or
modified to make this database structure work?
Sample 2
create or replace function base() returns trigger as $$begin
insert into pi (fluid_id) values (new.fluid_id)
where pr (contain) = 'ip';
I suppose you mean "where pr.contain = 'ip'" instead? pr is a table, not
a function.
You'd be in some interesting trouble if there'd be a function pr(text)
returning text - it would evaluate the function with the content of your
column and compare the result to your string.
ERROR: syntax error at or near "where" at character 41
QUERY: insert into pi (fluid_id) values ( $1 ) where pr (contain) = 'ip'
CONTEXT: PL/pgSQL function "base" line 2 at SQL statement
--
Alban Hertroys
No - I mean when each row of pr (contain) has 'ip' inserted as a value or
data.
Bob
----- Original Message -----
From: "Alban Hertroys" <alban@magproductions.nl>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgres General" <pgsql-general@postgresql.org>
Sent: Tuesday, November 15, 2005 9:42 AM
Subject: Re: [GENERAL] Is it databases in general, SQL or Postgresql?
Show quoted text
Bob Pawley wrote:
Sample 2 below, does not work. From a logical (perhaps naive) extension
of Sample 1, I adapted the function to identify which of the serial
numbers in table pr is to be transferred to table pi. I am attempting to
do this as part of the database structure _not_ as data retrieval.
Could someone explain to me why this isn't acceptable as a simple basic
function?
Could someone explain to me what needs to be changed, enhanced or
modified to make this database structure work?Sample 2
create or replace function base() returns trigger as $$begin
insert into pi (fluid_id) values (new.fluid_id)
where pr (contain) = 'ip';
I suppose you mean "where pr.contain = 'ip'" instead? pr is a table, not a
function.You'd be in some interesting trouble if there'd be a function pr(text)
returning text - it would evaluate the function with the content of your
column and compare the result to your string.ERROR: syntax error at or near "where" at character 41
QUERY: insert into pi (fluid_id) values ( $1 ) where pr (contain) = 'ip'
CONTEXT: PL/pgSQL function "base" line 2 at SQL statement
--
Alban Hertroys
On 11/15/05, Bob Pawley <rjpawley@shaw.ca> wrote:
create or replace function base() returns trigger as $$
begin
insert into pi (fluid_id) values (new.fluid_id)
where pr (contain) = 'ip';
return null;
end;
$$ language plpgsql;
Error Message �
ERROR: syntax error at or near "where" at character 41
QUERY: insert into pi (fluid_id) values ( $1 ) where pr (contain) =
'ip'CONTEXT: PL/pgSQL function "base" line 2 at SQL statement
this is SQL... the INSERT statement doesn't accept a WHERE clause
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
Thank you.
What clause is acceptable??
Bob
----- Original Message -----
From: "Jaime Casanova" <systemguards@gmail.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgre General" <pgsql-general@postgresql.org>
Sent: Tuesday, November 15, 2005 9:55 AM
Subject: Re: [GENERAL] Is it databases in general, SQL or Postgresql?
Show quoted text
On 11/15/05, Bob Pawley <rjpawley@shaw.ca> wrote:
create or replace function base() returns trigger as $$
begin
insert into pi (fluid_id) values (new.fluid_id)
where pr (contain) = 'ip';
return null;
end;
$$ language plpgsql;
Error Message �
ERROR: syntax error at or near "where" at character 41
QUERY: insert into pi (fluid_id) values ( $1 ) where pr (contain) =
'ip'CONTEXT: PL/pgSQL function "base" line 2 at SQL statementthis is SQL... the INSERT statement doesn't accept a WHERE clause
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
Bob Pawley wrote:
Hope someone can help me learn.
I highly suggest getting an entry level book on SQL and reading that, then going
through the PostgreSQL documentation. This will better equip you to solve these
problems, and no doubt get you much further ahead in a shorter period of time.
--
_______________________________
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________
Thank you very much for the suggestion. I have two books (1 general SQL and
1 specific for Postgre) , the documentation that comes with Postgre and
other web sources.
I have found no reference to how to structure this.
All of the references and the help I have so far received from the list
seems to be geared to data retreival. I am trying to move data, as part of
the structure, from one permanent table to another permanent as a primary
key.
Perhaps I am attempting a database structure that SQL, by ityself, will not
support.
Bob
----- Original Message -----
From: "Bricklen Anderson" <BAnderson@PresiNET.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgre General" <pgsql-general@postgresql.org>
Sent: Tuesday, November 15, 2005 10:00 AM
Subject: Re: [GENERAL] Is it databases in general, SQL or Postgresql?
Show quoted text
Bob Pawley wrote:
Hope someone can help me learn.
I highly suggest getting an entry level book on SQL and reading that, then
going
through the PostgreSQL documentation. This will better equip you to solve
these
problems, and no doubt get you much further ahead in a shorter period of
time.
--
_______________________________This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________
Bob Pawley wrote:
Thank you.
What clause is acceptable??
INSERT has no conditional clause. You send INSERT, it goes to the
database -- the database either accepts it or rejects it (unique key
violations, bad data, etc).
If you want to INSERT if some value = 'xxx', wrap an IF THEN statement
around it.
On 11/15/05, Bob Pawley <rjpawley@shaw.ca> wrote:
No - I mean when each row of pr (contain) has 'ip' inserted as a value or
data.Bob
create or replace function base() returns trigger as $$
begin
if new.contain = 'ip' then
insert into pi (fluid_id) values (new.fluid_id);
end if;
return null;
end;
$$ language plpgsql;
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
That works. Thanks very much.
Bob
----- Original Message -----
From: "Jaime Casanova" <systemguards@gmail.com>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Alban Hertroys" <alban@magproductions.nl>; "Postgres General"
<pgsql-general@postgresql.org>
Sent: Tuesday, November 15, 2005 11:36 AM
Subject: Re: [GENERAL] Is it databases in general, SQL or Postgresql?
On 11/15/05, Bob Pawley <rjpawley@shaw.ca> wrote:
No - I mean when each row of pr (contain) has 'ip' inserted as a value or
data.Bob
create or replace function base() returns trigger as $$
begin
if new.contain = 'ip' then
insert into pi (fluid_id) values (new.fluid_id);
end if;
return null;
end;
$$ language plpgsql;
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
It worked - Thank you
Bob
----- Original Message -----
From: "William Yu" <wyu@talisys.com>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, November 15, 2005 11:06 AM
Subject: Re: [GENERAL] Is it databases in general, SQL or Postgresql?
Show quoted text
Bob Pawley wrote:
Thank you.
What clause is acceptable??
INSERT has no conditional clause. You send INSERT, it goes to the
database -- the database either accepts it or rejects it (unique key
violations, bad data, etc).If you want to INSERT if some value = 'xxx', wrap an IF THEN statement
around it.---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
1) It's PostgreSQL or Postgres, not Postgre. :)
2) pgsql-sql is a better list for simple SQL questions
3) It would probably be more useful if you stated what you were actually
trying to do. One email had a function that it appears is running as
part of a trigger. If you really just need to move data from one table
to another, there's probably better ways to do it.
So, what are you really trying to do?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461