Thread: trigger that needs a PK

Hi,
I'm sure this question has been ask before but I could not find anything on
google.  I most likely did not enter the right text into the google search.

I have a parent table that requires that an insert into a child table happen.
The problem is I can not determine what the parent pk is for the insert into
the child because it hasn't happen yet - if I set the trigger to before
insert.  So I guess I need something that works with after insert into the
parent so the pkid can be created.

The parent has:
pkid serial
lot varchar 30

the child:
pkid serial
fk_parent int
other fields....

The question is how can I get the pk and insert it into the child.  Of course
I'm new so if I missed something just provide the link or tutorial.

Thanks in advance!


--
John Fabiani

am  Tue, dem 12.02.2008, um 23:20:46 -0800 mailte johnf folgendes:
> Hi,
> I'm sure this question has been ask before but I could not find anything on
> google.  I most likely did not enter the right text into the google search.
>
> I have a parent table that requires that an insert into a child table happen.
> The problem is I can not determine what the parent pk is for the insert into
> the child because it hasn't happen yet - if I set the trigger to before
> insert.  So I guess I need something that works with after insert into the
> parent so the pkid can be created.


You don't need a TRIGGER, you need currval(). I will explain with an
example:

test=# create table master (id serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "master_id_seq" for
serial column "master.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"master_pkey" for table "master"
CREATE TABLE
test=*# create table slave (fk int references master);
CREATE TABLE
test=*# insert into master (id) values(default);
INSERT 0 1
test=*# insert into master (id) values(default);
INSERT 0 1
test=*# insert into master (id) values(default);
INSERT 0 1
test=*# insert into slave (fk) values(currval('master_id_seq'));
INSERT 0 1
test=*# select * from master;
 id
----
  1
  2
  3
(3 rows)

test=*# select * from slave;
 fk
----
  3
(1 row)


http://www.postgresql.org/docs/current/static/functions-sequence.html


HTH, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Tuesday 12 February 2008 11:32:24 pm A. Kretschmer wrote:
> am  Tue, dem 12.02.2008, um 23:20:46 -0800 mailte johnf folgendes:
> > Hi,
> > I'm sure this question has been ask before but I could not find anything
> > on google.  I most likely did not enter the right text into the google
> > search.
> >
> > I have a parent table that requires that an insert into a child table
> > happen. The problem is I can not determine what the parent pk is for the
> > insert into the child because it hasn't happen yet - if I set the trigger
> > to before insert.  So I guess I need something that works with after
> > insert into the parent so the pkid can be created.
>
> You don't need a TRIGGER, you need currval(). I will explain with an
> example:
>
> test=# create table master (id serial primary key);
> NOTICE:  CREATE TABLE will create implicit sequence "master_id_seq" for
> serial column "master.id"
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
> "master_pkey" for table "master"
> CREATE TABLE
> test=*# create table slave (fk int references master);
> CREATE TABLE
> test=*# insert into master (id) values(default);
> INSERT 0 1
> test=*# insert into master (id) values(default);
> INSERT 0 1
> test=*# insert into master (id) values(default);
> INSERT 0 1
> test=*# insert into slave (fk) values(currval('master_id_seq'));
> INSERT 0 1
> test=*# select * from master;
>  id
> ----
>   1
>   2
>   3
> (3 rows)
>
> test=*# select * from slave;
>  fk
> ----
>   3
> (1 row)
>
>
> http://www.postgresql.org/docs/current/static/functions-sequence.html
>
>
> HTH, Andreas

But how do I do automaticly???

You code implies that I just string two inserts together.  I was hoping to use
rules or some other way to do it automaticly.

--
John Fabiani

am  Tue, dem 12.02.2008, um 23:46:41 -0800 mailte johnf folgendes:
> > http://www.postgresql.org/docs/current/static/functions-sequence.html
> >
> >
> > HTH, Andreas
>
> But how do I do automaticly???
>
> You code implies that I just string two inserts together.  I was hoping to use
> rules or some other way to do it automaticly.

currval() returns the last inserted value within the current session, it
is the usual way to insert into the parent table and use currval() to
obtain the last inserted serial value for inserts into the child table.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Wednesday 13 February 2008 12:35:27 am A. Kretschmer wrote:
> am  Tue, dem 12.02.2008, um 23:46:41 -0800 mailte johnf folgendes:
> > > http://www.postgresql.org/docs/current/static/functions-sequence.html
> > >
> > >
> > > HTH, Andreas
> >
> > But how do I do automaticly???
> >
> > You code implies that I just string two inserts together.  I was hoping
> > to use rules or some other way to do it automaticly.
>
> currval() returns the last inserted value within the current session, it
> is the usual way to insert into the parent table and use currval() to
> obtain the last inserted serial value for inserts into the child table.
>
>
> Andreas

After much reading I have the following code - please help me review:

-- Function: addrectolots()

-- DROP FUNCTION addrectolots();

CREATE OR REPLACE FUNCTION addrectolots()
  RETURNS "trigger" AS
$BODY$DECLARE
  next_aglot CURSOR FOR select currval('aglots_pkid_seq1') as fkey;
  --cur_aglot CURSOR FOR select aglots.pkid from public.aglots where
aglots.clot = new.clot;

  myaglot_id public.aglots.pkid%TYPE;
BEGIN


  --IF tg_op = 'INSERT' THEN
     insert into aglots (clot,fk_species,fk_variety,fk_agpoptrs) values
(new.clot,new.fk_species,new.fk_variety,new.pkid);
         OPEN next_aglot ;
     FETCH next_aglot INTO myaglot_id;
         new.fk_aglots := myaglot_id;
  --END IF;

  RETURN new;
END

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION addrectolots() OWNER TO johnf;
GRANT EXECUTE ON FUNCTION addrectolots() TO public;
GRANT EXECUTE ON FUNCTION addrectolots() TO johnf;

--
John Fabiani

"A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> am  Tue, dem 12.02.2008, um 23:20:46 -0800 mailte johnf folgendes:
>> The problem is I can not determine what the parent pk is for the insert into
>> the child because it hasn't happen yet - if I set the trigger to before
>> insert.  So I guess I need something that works with after insert into the
>> parent so the pkid can be created.

> You don't need a TRIGGER, you need currval().

I think this advice is entirely misleading.  As best I can tell, the
OP's problem is best solved with an ON INSERT trigger on the parent
table, and all he's got to do is look at the NEW field for the PK.
He is mistaken to think that the value won't have been assigned yet
when the trigger is fired (and if he was right, then currval would
be no solution either).  AFAICS using currval is just useless
complication.

            regards, tom lane

On Wednesday 13 February 2008 07:25:02 am Tom Lane wrote:
> "A. Kretschmer" <andreas.kretschmer@schollglas.com> writes:
> > am  Tue, dem 12.02.2008, um 23:20:46 -0800 mailte johnf folgendes:
> >> The problem is I can not determine what the parent pk is for the insert
> >> into the child because it hasn't happen yet - if I set the trigger to
> >> before insert.  So I guess I need something that works with after insert
> >> into the parent so the pkid can be created.
> >
> > You don't need a TRIGGER, you need currval().
>
> I think this advice is entirely misleading.  As best I can tell, the
> OP's problem is best solved with an ON INSERT trigger on the parent
> table, and all he's got to do is look at the NEW field for the PK.
> He is mistaken to think that the value won't have been assigned yet
> when the trigger is fired (and if he was right, then currval would
> be no solution either).  AFAICS using currval is just useless
> complication.
>
>             regards, tom lane

Thanks Tom,

I was able to discover the new had my PK after all.  I decided to use the
trigger and all appears to be working for the moment.


--
John Fabiani

Hello,


I'm struggling my brain for some days without success ...

I have three tables:

cages
reserved_cages
reserved_days


Inside cages, I want to display all the id > 0 and animal_type_id=1,
and I want to display all of them no matter if it has some
reserved_cages related, so I have to use a LEFT JOIN.

Ok, now, the reserved_cages must exist only when there are rows in the
reserved_days table.

What I'm getting, sometimes, is only the cages that has some
reserved_cages (because they have some reserved_days), and when I try
to display all of the cages, I can't exclude the ones that have id>0
or animal_type_id=1, I get all of them, so it seems the WHERE clausule
is not working ...

here are the code:


here is the initial cages that I want to display, no matter if they
have related data or not:

SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND  c.id >
0) ORDER BY order_position


this seems to work, but I get ALL the cages, no matter if they are
cages_type_id<>1  (I only want to display=1)

SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
(c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) AND r.id IN
(SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15')
ORDER BY order_position


if I change the first AND for a WHERE, like this:

SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
(c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0) AND r.id IN
(SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15')
ORDER BY order_position

I get only the cages that has some reservations on the date performed.


The relations between tables are:

cages: id
reserved_cages: cage_id
reserved_days: reserved_cage_id

So I have to query for a given day if there are reservations, pass
those rows to the reserved_cages (where I only store the date_in and
date_out).

I think I can use an extra field in the reserved_days adding a
cage_id, the SELECT would be much simpler and I think much faster, but
I'm trying to avoid duplicated data, and at the same time, learning
postgresql and try to find more or less the limitations, maybe those
limitations (if they're limitations) come from my head or from sql ...

as always, thanks for your help !


regards,


raimon

Rai Developer wrote:
> Hello,
>
>
> I'm struggling my brain for some days without success ...
>
> I have three tables:
>
> cages
> reserved_cages
> reserved_days
>
>
> Inside cages, I want to display all the id > 0 and animal_type_id=1, and
> I want to display all of them no matter if it has some reserved_cages
> related, so I have to use a LEFT JOIN.

I would think that every cage has a valid id - meaning the id>0 should
not be needed.

> Ok, now, the reserved_cages must exist only when there are rows in the
> reserved_days table.
>
> What I'm getting, sometimes, is only the cages that has some
> reserved_cages (because they have some reserved_days), and when I try to
> display all of the cages, I can't exclude the ones that have id>0 or
> animal_type_id=1, I get all of them, so it seems the WHERE clausule is
> not working ...
>
> here are the code:
>
>
> here is the initial cages that I want to display, no matter if they have
> related data or not:
>
> SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND  c.id >
> 0) ORDER BY order_position
>
>
> this seems to work, but I get ALL the cages, no matter if they are
> cages_type_id<>1  (I only want to display=1)
>
> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) AND r.id IN
> (SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15')
> ORDER BY order_position

SELECT c.*, r.*
FROM cages c
LEFT JOIN reserved_cages r ON (c.id=r.cage_id)

WHERE ( c.cages_type_id=1 AND  c.id > 0)
AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
date='2008-02-15')

ORDER BY order_position


The main problem is the where clause from above got merged into the left
join clause.

That would give you the cages with a reservation on the date.
Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN (SELECT..." to
get the ones without a reservation.


I changed the AND r.id to AND c.id - I feel certain you want the cage id
not the id of the reservation entry matched against the reserved_cage_id
from reserved_days.


>
>
> if I change the first AND for a WHERE, like this:
>
> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0) AND r.id IN
> (SELECT reserved_cage_id FROM reserved_days WHERE date='2008-02-15')
> ORDER BY order_position
>
> I get only the cages that has some reservations on the date performed.
>
>
> The relations between tables are:
>
> cages: id
> reserved_cages: cage_id
> reserved_days: reserved_cage_id
>
> So I have to query for a given day if there are reservations, pass those
> rows to the reserved_cages (where I only store the date_in and date_out).
>
> I think I can use an extra field in the reserved_days adding a cage_id,
> the SELECT would be much simpler and I think much faster, but I'm trying
> to avoid duplicated data, and at the same time, learning postgresql and
> try to find more or less the limitations, maybe those limitations (if
> they're limitations) come from my head or from sql ...
>
> as always, thanks for your help !

reserved_cages would seem to be unnecessary as you can get the list of
reserved cage id's from the SELECT -

SELECT c.id
FROM cages c

WHERE ( c.cages_type_id=1 AND  c.id > 0)
AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
date='2008-02-15')

ORDER BY order_position


If you are using the reserved_cages to hold data for who has reserved
it, I would think it be better stored in the reserved_days table. (at
least the id of the customer reserving the cage)



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Hello,



On 16/02/2008, at 14:23, Shane Ambler wrote:

> Rai Developer wrote:
>> Hello,
>> I'm struggling my brain for some days without success ...
>> I have three tables:
>> cages
>> reserved_cages
>> reserved_days
>> Inside cages, I want to display all the id > 0 and
>> animal_type_id=1, and I want to display all of them no matter if it
>> has some reserved_cages related, so I have to use a LEFT JOIN.
>
> I would think that every cage has a valid id - meaning the id>0
> should not be needed.

yes, I need it, because there are special cages that have a negative id


>> Ok, now, the reserved_cages must exist only when there are rows in
>> the reserved_days table.
>> What I'm getting, sometimes, is only the cages that has some
>> reserved_cages (because they have some reserved_days), and when I
>> try to display all of the cages, I can't exclude the ones that have
>> id>0 or animal_type_id=1, I get all of them, so it seems the WHERE
>> clausule is not working ...
>> here are the code:
>> here is the initial cages that I want to display, no matter if they
>> have related data or not:
>> SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND
>> c.id > 0) ORDER BY order_position
>> this seems to work, but I get ALL the cages, no matter if they are
>> cages_type_id<>1  (I only want to display=1)
>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
>> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) AND r.id IN
>> (SELECT reserved_cage_id FROM reserved_days WHERE
>> date='2008-02-15')   ORDER BY order_position
>
> SELECT c.*, r.*
> FROM cages c
> LEFT JOIN reserved_cages r ON (c.id=r.cage_id)
>
> WHERE ( c.cages_type_id=1 AND  c.id > 0)
> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
> date='2008-02-15')
>
> ORDER BY order_position
>
>
> The main problem is the where clause from above got merged into the
> left join clause.
>
> That would give you the cages with a reservation on the date.
> Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN (SELECT..."
> to get the ones without a reservation.

I need to display ALL the cages, no matter if they have a reservation
or not on the passed day


>
> I changed the AND r.id to AND c.id - I feel certain you want the
> cage id not the id of the reservation entry matched against the
> reserved_cage_id from reserved_days.

well, the id from IN (SELECT reserved_cage_id FROM reserved_days WHERE
date='2008-02-15') is the id of the reserved_cages, I can't change
into this AND r.id to AND c.id becaue they are different id values.

basically, what I want is display the name of the customer who has the
reservation on the current cage and in the passed period. in the
reserved_cage I have the date_in and date_out of the reservation, and
in the reserved_days I have one row for each day the reservations
occur, with other data that I need.

maybe I can change the SELECT for using only two tables, using ....
WHERE (reserved_cage.date_in <= '2008-02-15') AND
(reserved_cage.date_out >= '2008-02-15')

maybe this also works, and I use only two tables:

SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
(c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0)  AND
(r.date_in <= '2008-02-15') AND  (r.date_out >= '2008-02-15') ORDER BY
order_position

but again, I think the WHERE clausule is affecting the LEFT JOIN ....
and I'm only getting the cages with some reservation ...

any idea on how to change it for showing all the cages, no matter if
they have reservation or not ?


thanks again,


raimon



>
>> if I change the first AND for a WHERE, like this:
>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
>> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0) AND r.id
>> IN (SELECT reserved_cage_id FROM reserved_days WHERE
>> date='2008-02-15')   ORDER BY order_position
>> I get only the cages that has some reservations on the date
>> performed.
>> The relations between tables are:
>> cages: id
>> reserved_cages: cage_id
>> reserved_days: reserved_cage_id
>> So I have to query for a given day if there are reservations, pass
>> those rows to the reserved_cages (where I only store the date_in
>> and date_out).
>> I think I can use an extra field in the reserved_days adding a
>> cage_id, the SELECT would be much simpler and I think much faster,
>> but I'm trying to avoid duplicated data, and at the same time,
>> learning postgresql and try to find more or less the limitations,
>> maybe those limitations (if they're limitations) come from my head
>> or from sql ...
>> as always, thanks for your help !
>
> reserved_cages would seem to be unnecessary as you can get the list
> of reserved cage id's from the SELECT -
>
> SELECT c.id
> FROM cages c
>
> WHERE ( c.cages_type_id=1 AND  c.id > 0)
> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
> date='2008-02-15')
>
> ORDER BY order_position
>
>
> If you are using the reserved_cages to hold data for who has
> reserved it, I would think it be better stored in the reserved_days
> table. (at least the id of the customer reserving the cage)
>
>
>
> --
>
> Shane Ambler
> pgSQL (at) Sheeky (dot) Biz
>
> Get Sheeky @ http://Sheeky.Biz
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>              http://archives.postgresql.org
>

Sorry for replying on top ...

I can do it like this ...

CREATE TEMP TABLE d_reserved_cages AS SELECT * FROM reserved_cages r
WHERE (r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') ;

SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON
(c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) ORDER BY
order_position

DROP TABLE d_reserved_cages;

but I would prefer a direct solution without using/creating extra
tables, I think it shouldn't be too complicated ... but at least it is
for me ...

thanks,


raimon


On 16/02/2008, at 15:41, Rai Developer wrote:

> Hello,
>
>
>
> On 16/02/2008, at 14:23, Shane Ambler wrote:
>
>> Rai Developer wrote:
>>> Hello,
>>> I'm struggling my brain for some days without success ...
>>> I have three tables:
>>> cages
>>> reserved_cages
>>> reserved_days
>>> Inside cages, I want to display all the id > 0 and
>>> animal_type_id=1, and I want to display all of them no matter if
>>> it has some reserved_cages related, so I have to use a LEFT JOIN.
>>
>> I would think that every cage has a valid id - meaning the id>0
>> should not be needed.
>
> yes, I need it, because there are special cages that have a negative
> id
>
>
>>> Ok, now, the reserved_cages must exist only when there are rows in
>>> the reserved_days table.
>>> What I'm getting, sometimes, is only the cages that has some
>>> reserved_cages (because they have some reserved_days), and when I
>>> try to display all of the cages, I can't exclude the ones that
>>> have id>0 or animal_type_id=1, I get all of them, so it seems the
>>> WHERE clausule is not working ...
>>> here are the code:
>>> here is the initial cages that I want to display, no matter if
>>> they have related data or not:
>>> SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND
>>> c.id > 0) ORDER BY order_position
>>> this seems to work, but I get ALL the cages, no matter if they are
>>> cages_type_id<>1  (I only want to display=1)
>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
>>> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) AND r.id
>>> IN (SELECT reserved_cage_id FROM reserved_days WHERE
>>> date='2008-02-15')   ORDER BY order_position
>>
>> SELECT c.*, r.*
>> FROM cages c
>> LEFT JOIN reserved_cages r ON (c.id=r.cage_id)
>>
>> WHERE ( c.cages_type_id=1 AND  c.id > 0)
>> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
>> date='2008-02-15')
>>
>> ORDER BY order_position
>>
>>
>> The main problem is the where clause from above got merged into the
>> left join clause.
>>
>> That would give you the cages with a reservation on the date.
>> Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN (SELECT..."
>> to get the ones without a reservation.
>
> I need to display ALL the cages, no matter if they have a
> reservation or not on the passed day
>
>
>>
>> I changed the AND r.id to AND c.id - I feel certain you want the
>> cage id not the id of the reservation entry matched against the
>> reserved_cage_id from reserved_days.
>
> well, the id from IN (SELECT reserved_cage_id FROM reserved_days
> WHERE date='2008-02-15') is the id of the reserved_cages, I can't
> change into this AND r.id to AND c.id becaue they are different id
> values.
>
> basically, what I want is display the name of the customer who has
> the reservation on the current cage and in the passed period. in the
> reserved_cage I have the date_in and date_out of the reservation,
> and in the reserved_days I have one row for each day the
> reservations occur, with other data that I need.
>
> maybe I can change the SELECT for using only two tables, using ....
> WHERE (reserved_cage.date_in <= '2008-02-15') AND
> (reserved_cage.date_out >= '2008-02-15')
>
> maybe this also works, and I use only two tables:
>
> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0)  AND
> (r.date_in <= '2008-02-15') AND  (r.date_out >= '2008-02-15') ORDER
> BY order_position
>
> but again, I think the WHERE clausule is affecting the LEFT
> JOIN .... and I'm only getting the cages with some reservation ...
>
> any idea on how to change it for showing all the cages, no matter if
> they have reservation or not ?
>
>
> thanks again,
>
>
> raimon
>
>
>
>>
>>> if I change the first AND for a WHERE, like this:
>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
>>> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0) AND r.id
>>> IN (SELECT reserved_cage_id FROM reserved_days WHERE
>>> date='2008-02-15')   ORDER BY order_position
>>> I get only the cages that has some reservations on the date
>>> performed.
>>> The relations between tables are:
>>> cages: id
>>> reserved_cages: cage_id
>>> reserved_days: reserved_cage_id
>>> So I have to query for a given day if there are reservations, pass
>>> those rows to the reserved_cages (where I only store the date_in
>>> and date_out).
>>> I think I can use an extra field in the reserved_days adding a
>>> cage_id, the SELECT would be much simpler and I think much faster,
>>> but I'm trying to avoid duplicated data, and at the same time,
>>> learning postgresql and try to find more or less the limitations,
>>> maybe those limitations (if they're limitations) come from my head
>>> or from sql ...
>>> as always, thanks for your help !
>>
>> reserved_cages would seem to be unnecessary as you can get the list
>> of reserved cage id's from the SELECT -
>>
>> SELECT c.id
>> FROM cages c
>>
>> WHERE ( c.cages_type_id=1 AND  c.id > 0)
>> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
>> date='2008-02-15')
>>
>> ORDER BY order_position
>>
>>
>> If you are using the reserved_cages to hold data for who has
>> reserved it, I would think it be better stored in the reserved_days
>> table. (at least the id of the customer reserving the cage)
>>
>>
>>
>> --
>>
>> Shane Ambler
>> pgSQL (at) Sheeky (dot) Biz
>>
>> Get Sheeky @ http://Sheeky.Biz
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>>             http://archives.postgresql.org
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>      choose an index scan if your joining column's datatypes do not
>      match
>

On 16/02/2008, at 20:53, Rai Developer wrote:

> Sorry for replying on top ...
>
> I can do it like this ...
>
> CREATE TEMP TABLE d_reserved_cages AS SELECT * FROM reserved_cages r
> WHERE (r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') ;
>
> SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON
> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) ORDER BY
> order_position

There was a mistake, the first AND must be WHERE ...

SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON
(c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0) ORDER BY
order_position


r.



> DROP TABLE d_reserved_cages;
>
> but I would prefer a direct solution without using/creating extra
> tables, I think it shouldn't be too complicated ... but at least it
> is for me ...
>
> thanks,
>
>
> raimon
>
>
> On 16/02/2008, at 15:41, Rai Developer wrote:
>
>> Hello,
>>
>>
>>
>> On 16/02/2008, at 14:23, Shane Ambler wrote:
>>
>>> Rai Developer wrote:
>>>> Hello,
>>>> I'm struggling my brain for some days without success ...
>>>> I have three tables:
>>>> cages
>>>> reserved_cages
>>>> reserved_days
>>>> Inside cages, I want to display all the id > 0 and
>>>> animal_type_id=1, and I want to display all of them no matter if
>>>> it has some reserved_cages related, so I have to use a LEFT JOIN.
>>>
>>> I would think that every cage has a valid id - meaning the id>0
>>> should not be needed.
>>
>> yes, I need it, because there are special cages that have a
>> negative id
>>
>>
>>>> Ok, now, the reserved_cages must exist only when there are rows
>>>> in the reserved_days table.
>>>> What I'm getting, sometimes, is only the cages that has some
>>>> reserved_cages (because they have some reserved_days), and when I
>>>> try to display all of the cages, I can't exclude the ones that
>>>> have id>0 or animal_type_id=1, I get all of them, so it seems the
>>>> WHERE clausule is not working ...
>>>> here are the code:
>>>> here is the initial cages that I want to display, no matter if
>>>> they have related data or not:
>>>> SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND
>>>> c.id > 0) ORDER BY order_position
>>>> this seems to work, but I get ALL the cages, no matter if they
>>>> are cages_type_id<>1  (I only want to display=1)
>>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
>>>> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) AND r.id
>>>> IN (SELECT reserved_cage_id FROM reserved_days WHERE
>>>> date='2008-02-15')   ORDER BY order_position
>>>
>>> SELECT c.*, r.*
>>> FROM cages c
>>> LEFT JOIN reserved_cages r ON (c.id=r.cage_id)
>>>
>>> WHERE ( c.cages_type_id=1 AND  c.id > 0)
>>> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
>>> date='2008-02-15')
>>>
>>> ORDER BY order_position
>>>
>>>
>>> The main problem is the where clause from above got merged into
>>> the left join clause.
>>>
>>> That would give you the cages with a reservation on the date.
>>> Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN
>>> (SELECT..." to get the ones without a reservation.
>>
>> I need to display ALL the cages, no matter if they have a
>> reservation or not on the passed day
>>
>>
>>>
>>> I changed the AND r.id to AND c.id - I feel certain you want the
>>> cage id not the id of the reservation entry matched against the
>>> reserved_cage_id from reserved_days.
>>
>> well, the id from IN (SELECT reserved_cage_id FROM reserved_days
>> WHERE date='2008-02-15') is the id of the reserved_cages, I can't
>> change into this AND r.id to AND c.id becaue they are different id
>> values.
>>
>> basically, what I want is display the name of the customer who has
>> the reservation on the current cage and in the passed period. in
>> the reserved_cage I have the date_in and date_out of the
>> reservation, and in the reserved_days I have one row for each day
>> the reservations occur, with other data that I need.
>>
>> maybe I can change the SELECT for using only two tables, using ....
>> WHERE (reserved_cage.date_in <= '2008-02-15') AND
>> (reserved_cage.date_out >= '2008-02-15')
>>
>> maybe this also works, and I use only two tables:
>>
>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
>> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0)  AND
>> (r.date_in <= '2008-02-15') AND  (r.date_out >= '2008-02-15') ORDER
>> BY order_position
>>
>> but again, I think the WHERE clausule is affecting the LEFT
>> JOIN .... and I'm only getting the cages with some reservation ...
>>
>> any idea on how to change it for showing all the cages, no matter
>> if they have reservation or not ?
>>
>>
>> thanks again,
>>
>>
>> raimon
>>
>>
>>
>>>
>>>> if I change the first AND for a WHERE, like this:
>>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
>>>> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0) AND
>>>> r.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
>>>> date='2008-02-15')   ORDER BY order_position
>>>> I get only the cages that has some reservations on the date
>>>> performed.
>>>> The relations between tables are:
>>>> cages: id
>>>> reserved_cages: cage_id
>>>> reserved_days: reserved_cage_id
>>>> So I have to query for a given day if there are reservations,
>>>> pass those rows to the reserved_cages (where I only store the
>>>> date_in and date_out).
>>>> I think I can use an extra field in the reserved_days adding a
>>>> cage_id, the SELECT would be much simpler and I think much
>>>> faster, but I'm trying to avoid duplicated data, and at the same
>>>> time, learning postgresql and try to find more or less the
>>>> limitations, maybe those limitations (if they're limitations)
>>>> come from my head or from sql ...
>>>> as always, thanks for your help !
>>>
>>> reserved_cages would seem to be unnecessary as you can get the
>>> list of reserved cage id's from the SELECT -
>>>
>>> SELECT c.id
>>> FROM cages c
>>>
>>> WHERE ( c.cages_type_id=1 AND  c.id > 0)
>>> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
>>> date='2008-02-15')
>>>
>>> ORDER BY order_position
>>>
>>>
>>> If you are using the reserved_cages to hold data for who has
>>> reserved it, I would think it be better stored in the
>>> reserved_days table. (at least the id of the customer reserving
>>> the cage)
>>>
>>>
>>>
>>> --
>>>
>>> Shane Ambler
>>> pgSQL (at) Sheeky (dot) Biz
>>>
>>> Get Sheeky @ http://Sheeky.Biz
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 4: Have you searched our list archives?
>>>
>>>            http://archives.postgresql.org
>>>
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>     choose an index scan if your joining column's datatypes do not
>>     match
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>

Rai Developer wrote:

> maybe this also works, and I use only two tables:
>
> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0)  AND
> (r.date_in <= '2008-02-15') AND  (r.date_out >= '2008-02-15') ORDER
> BY order_position
>
> but again, I think the WHERE clausule is affecting the LEFT JOIN ....
> and I'm only getting the cages with some reservation ...
>
> any idea on how to change it for showing all the cages, no matter if
> they have reservation or not ?


So I take it that date_out will be the date it goes to the customer and
date_in is when it returns to you.


I had a bit more of a look this time and tested this one -


SELECT c.*,r.*

FROM cages c
LEFT JOIN reserved_cages r ON (c.id=r.cage_id)
AND ('2008-02-15' BETWEEN r.date_out AND r.date_in)

WHERE ( c.cages_type_id=1 AND  c.id > 0)

ORDER BY order_position


Two things I can think of this way - is if the cage is advanced reserved
for 208-02-17 - 2008-02-19 and you search for 2008-02-15 and they want
it from 2008-02-15 for four days you won't see the advanced booking
which will clash.
Also if the date_out is set but not the date_in you won't see that it is
out indefinitely.



So you may want to use sub-selects (I merged the reservation detail into
one column for simplicity) -

SELECT c.*,
(SELECT r.date_out||' '||r.customer
FROM reserved_cages r
WHERE c.id=r.cage_id
AND ( ('2008-02-15' BETWEEN r.date_out AND r.date_in)
OR (r.date_out<='2008-02-15' AND r.date_in IS NULL)
OR (r.date_out>='2008-02-15')
) ORDER BY r.date_out LIMIT 1 ) as reserved


FROM cages c

WHERE ( c.cages_type_id=1 AND  c.id > 0)

ORDER BY order_position



(I'm sure some of the pro's can come up with something better though)



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

On Sat, 16 Feb 2008, Rai Developer wrote:

> Sorry for replying on top ...
>
> I can do it like this ...
>
> CREATE TEMP TABLE d_reserved_cages AS SELECT * FROM reserved_cages r
> WHERE (r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') ;
>
> SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON
> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) ORDER BY
> order_position

You should be able to put what you have as the temp table definition query
as a subselect in join probably. So something like the following:

SELECT c.*,r.* FROM cages c LEFT JOIN (SELECT * from reserved_cages r
WHERE r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') r ON
(c.id=r.cage_id) WHERE (c.cages_type_id=1 AND c.id>0) ORDER BY
order_position;

Shane and Stephan,


thanks for your ideas, I'be veen very busy but I'm going to try them
as soon as possible ...

regards,

raimon



On 16/02/2008, at 21:39, Rai Developer wrote:

>
> On 16/02/2008, at 20:53, Rai Developer wrote:
>
>> Sorry for replying on top ...
>>
>> I can do it like this ...
>>
>> CREATE TEMP TABLE d_reserved_cages AS SELECT * FROM reserved_cages
>> r WHERE (r.date_in <= '2008-02-15' AND r.date_out >= '2008-02-15') ;
>>
>> SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON
>> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) ORDER BY
>> order_position
>
> There was a mistake, the first AND must be WHERE ...
>
> SELECT c.*, r.* FROM cages c LEFT JOIN d_reserved_cages r ON
> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0) ORDER BY
> order_position
>
>
> r.
>
>
>
>> DROP TABLE d_reserved_cages;
>>
>> but I would prefer a direct solution without using/creating extra
>> tables, I think it shouldn't be too complicated ... but at least it
>> is for me ...
>>
>> thanks,
>>
>>
>> raimon
>>
>>
>> On 16/02/2008, at 15:41, Rai Developer wrote:
>>
>>> Hello,
>>>
>>>
>>>
>>> On 16/02/2008, at 14:23, Shane Ambler wrote:
>>>
>>>> Rai Developer wrote:
>>>>> Hello,
>>>>> I'm struggling my brain for some days without success ...
>>>>> I have three tables:
>>>>> cages
>>>>> reserved_cages
>>>>> reserved_days
>>>>> Inside cages, I want to display all the id > 0 and
>>>>> animal_type_id=1, and I want to display all of them no matter if
>>>>> it has some reserved_cages related, so I have to use a LEFT JOIN.
>>>>
>>>> I would think that every cage has a valid id - meaning the id>0
>>>> should not be needed.
>>>
>>> yes, I need it, because there are special cages that have a
>>> negative id
>>>
>>>
>>>>> Ok, now, the reserved_cages must exist only when there are rows
>>>>> in the reserved_days table.
>>>>> What I'm getting, sometimes, is only the cages that has some
>>>>> reserved_cages (because they have some reserved_days), and when
>>>>> I try to display all of the cages, I can't exclude the ones that
>>>>> have id>0 or animal_type_id=1, I get all of them, so it seems
>>>>> the WHERE clausule is not working ...
>>>>> here are the code:
>>>>> here is the initial cages that I want to display, no matter if
>>>>> they have related data or not:
>>>>> SELECT c.id, c.name FROM cages c WHERE ( c.cages_type_id=1 AND
>>>>> c.id > 0) ORDER BY order_position
>>>>> this seems to work, but I get ALL the cages, no matter if they
>>>>> are cages_type_id<>1  (I only want to display=1)
>>>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
>>>>> (c.id=r.cage_id) AND ( c.cages_type_id=1 AND  c.id > 0) AND r.id
>>>>> IN (SELECT reserved_cage_id FROM reserved_days WHERE
>>>>> date='2008-02-15')   ORDER BY order_position
>>>>
>>>> SELECT c.*, r.*
>>>> FROM cages c
>>>> LEFT JOIN reserved_cages r ON (c.id=r.cage_id)
>>>>
>>>> WHERE ( c.cages_type_id=1 AND  c.id > 0)
>>>> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
>>>> date='2008-02-15')
>>>>
>>>> ORDER BY order_position
>>>>
>>>>
>>>> The main problem is the where clause from above got merged into
>>>> the left join clause.
>>>>
>>>> That would give you the cages with a reservation on the date.
>>>> Change the "AND c.id IN (SELECT..." to "AND c.id NOT IN
>>>> (SELECT..." to get the ones without a reservation.
>>>
>>> I need to display ALL the cages, no matter if they have a
>>> reservation or not on the passed day
>>>
>>>
>>>>
>>>> I changed the AND r.id to AND c.id - I feel certain you want the
>>>> cage id not the id of the reservation entry matched against the
>>>> reserved_cage_id from reserved_days.
>>>
>>> well, the id from IN (SELECT reserved_cage_id FROM reserved_days
>>> WHERE date='2008-02-15') is the id of the reserved_cages, I can't
>>> change into this AND r.id to AND c.id becaue they are different id
>>> values.
>>>
>>> basically, what I want is display the name of the customer who has
>>> the reservation on the current cage and in the passed period. in
>>> the reserved_cage I have the date_in and date_out of the
>>> reservation, and in the reserved_days I have one row for each day
>>> the reservations occur, with other data that I need.
>>>
>>> maybe I can change the SELECT for using only two tables,
>>> using .... WHERE (reserved_cage.date_in <= '2008-02-15') AND
>>> (reserved_cage.date_out >= '2008-02-15')
>>>
>>> maybe this also works, and I use only two tables:
>>>
>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
>>> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0)  AND
>>> (r.date_in <= '2008-02-15') AND  (r.date_out >= '2008-02-15')
>>> ORDER BY order_position
>>>
>>> but again, I think the WHERE clausule is affecting the LEFT
>>> JOIN .... and I'm only getting the cages with some reservation ...
>>>
>>> any idea on how to change it for showing all the cages, no matter
>>> if they have reservation or not ?
>>>
>>>
>>> thanks again,
>>>
>>>
>>> raimon
>>>
>>>
>>>
>>>>
>>>>> if I change the first AND for a WHERE, like this:
>>>>> SELECT c.*, r.* FROM cages c LEFT JOIN reserved_cages r ON
>>>>> (c.id=r.cage_id) WHERE ( c.cages_type_id=1 AND  c.id > 0) AND
>>>>> r.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
>>>>> date='2008-02-15')   ORDER BY order_position
>>>>> I get only the cages that has some reservations on the date
>>>>> performed.
>>>>> The relations between tables are:
>>>>> cages: id
>>>>> reserved_cages: cage_id
>>>>> reserved_days: reserved_cage_id
>>>>> So I have to query for a given day if there are reservations,
>>>>> pass those rows to the reserved_cages (where I only store the
>>>>> date_in and date_out).
>>>>> I think I can use an extra field in the reserved_days adding a
>>>>> cage_id, the SELECT would be much simpler and I think much
>>>>> faster, but I'm trying to avoid duplicated data, and at the same
>>>>> time, learning postgresql and try to find more or less the
>>>>> limitations, maybe those limitations (if they're limitations)
>>>>> come from my head or from sql ...
>>>>> as always, thanks for your help !
>>>>
>>>> reserved_cages would seem to be unnecessary as you can get the
>>>> list of reserved cage id's from the SELECT -
>>>>
>>>> SELECT c.id
>>>> FROM cages c
>>>>
>>>> WHERE ( c.cages_type_id=1 AND  c.id > 0)
>>>> AND c.id IN (SELECT reserved_cage_id FROM reserved_days WHERE
>>>> date='2008-02-15')
>>>>
>>>> ORDER BY order_position
>>>>
>>>>
>>>> If you are using the reserved_cages to hold data for who has
>>>> reserved it, I would think it be better stored in the
>>>> reserved_days table. (at least the id of the customer reserving
>>>> the cage)
>>>>
>>>>
>>>>
>>>> --
>>>>
>>>> Shane Ambler
>>>> pgSQL (at) Sheeky (dot) Biz
>>>>
>>>> Get Sheeky @ http://Sheeky.Biz
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 4: Have you searched our list archives?
>>>>
>>>>           http://archives.postgresql.org
>>>>
>>>
>>>
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>>    choose an index scan if your joining column's datatypes do not
>>>    match
>>>
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>     subscribe-nomail command to majordomo@postgresql.org so that your
>>     message can get through to the mailing list cleanly
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>              http://archives.postgresql.org
>