Thread: 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
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? > 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? > 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 ;) >
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? > 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? > 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? > > http://archives.postgresql.org
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
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