Thread: Where Statement

On Wed, Nov 09, 2005 at 10:06:06AM -0800, Bob Pawley wrote:
> The following transfers the serial number of fluid_id into the two
> tables, specification and pipe.
>
> However, it doesn't follow the WHERE instruction. All fluid_id serial
> numbers in process are transferred to fluid_id column in pipe with no
> discrimination.

Could you post a minimal but complete example with all create,
insert, and select statements necessary to reproduce the problem?
What version of PostgreSQL are you running?

What's the purpose of "create table pro as" when you drop it without
doing anything?  Or does the actual code do something before the drop?

--
Michael Fuhr

[Please copy the mailing list on replies so others can contribute
to and learn from the discussion.]

On Wed, Nov 09, 2005 at 11:06:22AM -0800, Bob Pawley wrote:
> The postgre is version 8 - the about calls it version 1.2.1 March 28, 2005

What version exactly?  What does "SELECT version()" show?  It might
not matter but let's find out anyway.

> The following is the insert command -
> ------
> insert into process (fluid, process_name, ip_op_reactor, source,
> destination)
>  values ('boiler_water','drum1', 'op', 'city', 'process')
> -------
> Without "create table pro as" I get an error message that select is looking
> for a target. So I created a temporary table as a target. I drop the table
> after the transaction since if I don't drop it, on the next insert, I get
> message "table pro already exists". If I simply create a temporary table it
> persists until the end of a session, well beyond any transaction, giving me
> the same error message ("table pro already exists") on the second insert..

You can use PERFORM to execute a command and discard the result,
but it's not clear why you're doing the select at all.  Here's the
function you posted:

> create or replace function base() returns trigger as $$
> begin
>
> insert into specification (fluid_id) values (new.fluid_id);
>
> create table pro as
> select fluid_id from process where ip_op_reactor = 'ip';
> insert into pipe (fluid_id) values (new.fluid_id);
>
> drop table pro ;
> return null;
>
> end;
> $$ language plpgsql;

You create a table and populate it, then you do an unrelated insert,
then you drop the table.  What's the purpose of doing this?  What
are you wanting to happen that won't happen if you omit this code?

> Other than the permanent tables, the above and commands in my first e-mail
> are all that I am using.

A complete example would show us how to reproduce the problem, which
makes it easier to investigate.  Without a complete example, if
anybody wants to run the code you're posting then they have to spend
time writing their own code to fill in the missing pieces, guessing
what those pieces look like, perhaps incorrectly.  If they can't
reproduce the problem then they don't know what difference between
their system and yours accounts for the discrepancy.  Finding the
answer would be easier -- and you'd get a solution sooner -- if we
could see everything you're doing.  That means all CREATE statements
to set up the identical situation in an empty database, and all
INSERT, SELECT, etc., statements that lead to the results you're
seeing.  It would also be useful to see the actual output you're
getting, along with an explanation of how it differs from what you
were expecting.

--
Michael Fuhr