Thread: Is it databases in general, SQL or Postgresql?

Is it databases in general, SQL or Postgresql?

From
Bob Pawley
Date:
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

Re: Is it databases in general, SQL or Postgresql?

From
Bob Pawley
Date:
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


Re: Is it databases in general, SQL or Postgresql?

From
Jaime Casanova
Date:
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 ;)

Re: Is it databases in general, SQL or Postgresql?

From
Bob Pawley
Date:
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 ;)
>


Re: Is it databases in general, SQL or Postgresql?

From
Bricklen Anderson
Date:
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.
_______________________________

Re: Is it databases in general, SQL or Postgresql?

From
Bob Pawley
Date:
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.
> _______________________________


Re: Is it databases in general, SQL or Postgresql?

From
William Yu
Date:
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.

Re: Is it databases in general, SQL or Postgresql?

From
Jaime Casanova
Date:
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 ;)

Re: Is it databases in general, SQL or Postgresql?

From
Bob Pawley
Date:
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 ;)


Re: Is it databases in general, SQL or Postgresql?

From
Bob Pawley
Date:
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

Re: Is it databases in general, SQL or Postgresql?

From
"Jim C. Nasby"
Date:
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

Re: Is it databases in general, SQL or Postgresql?

From
Alban Hertroys
Date:
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