[SQL] Foreign Key: what value?

[Davi Leal]

Hi,
How to know the value which I must set in the foreign key field?. I have two 
tables:


CREATE TABLE AA (
  Id  SERIAL PRIMARY KEY,
  data char(9)
);

CREATE TABLE BB (
  BB_Id integer REFERENCES AA(Id) NOT NULL,
  field char(5)
);




I insert a register on table AA,

   INSERT INTO AA (data) VALUES ('123456789');


and then, I want to insert a related register in table BB, but I do not
know how get the proper value to the Foreign key BB_Id. Note that a lot of 
client are inserting at the same time on the AA table, so I can not just 
get the greater value of AA.Id

Maybe using transactions ?. Any tip, URI, ... will be welcome.

   INSERT INTO BB (BB_Id, field) VALUES (??,'12345');


Regards,
Davi

---(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

Re: [SQL] Foreign Key: what value?

[A. Kretschmer]

am  05.07.2006, um 17:19:26 +0200 mailte Davi Leal folgendes:
> Hi,
> How to know the value which I must set in the foreign key field?. I have two 
> tables:
> 
> 
> CREATE TABLE AA (
>   Id  SERIAL PRIMARY KEY,
>   data char(9)
> );
> 
> CREATE TABLE BB (
>   BB_Id integer REFERENCES AA(Id) NOT NULL,
>   field char(5)
> );
> 
> 
> 
> 
> I insert a register on table AA,
> 
>INSERT INTO AA (data) VALUES ('123456789');
> 
> 
> and then, I want to insert a related register in table BB, but I do not
> know how get the proper value to the Foreign key BB_Id. Note that a lot 
> of 

currval() is your friend:
17:33 < akretschmer> ??currval
17:33 < pg_docbot_ads> For information about 'currval' see:
17:33 < pg_docbot_ads> 
http://www.postgresql.org/docs/current/static/functions-sequence.html


HTH, Andreas
-- 
Andreas Kretschmer(Kontakt: siehe Header)
Heynitz:  035242/47215,  D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===Schollglas Unternehmensgruppe=== 

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] Problem with array subscripts in plpgsql trigger function

[Erik Jones]

Ok, I have a trigger set up on the following (stripped down) table:

CREATE TABLE members (
member_id   bigint,
member_status_id   smallint,
member_is_deletedboolean
);

Here's a shortened version of the trigger function:

CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER AS $um$
DECLARE
   status_deltas integer[];
BEGIN
   IF(NEW.member_status_id != OLD.member_status_id AND 
NEW.member_is_deleted IS NOT TRUE) THEN

  status_deltas[NEW.member_status_id] := 1;
  status_deltas[OLD.member_status_id] := -1;
   END IF;
   /*and after a couple more such conditional assignments I use the 
values in status_deltas to update another table holding status totals here*/

END;
$um$ LANGUAGE plpgsql;

on the two lines that access set array values I'm getting the following 
error:


ERROR:  invalid array subscripts

What gives?

--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Problem with array subscripts in plpgsql trigger function

[Aaron Bono]

On 7/5/06, Erik Jones <[EMAIL PROTECTED]> wrote:
Ok, I have a trigger set up on the following (stripped down) table:CREATE TABLE members (member_id   bigint,member_status_id   smallint,member_is_deletedboolean);Here's a shortened version of the trigger function:
CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER AS $um$DECLAREstatus_deltas integer[];BEGINIF(NEW.member_status_id != OLD.member_status_id ANDNEW.member_is_deleted IS NOT TRUE) THEN
   status_deltas[NEW.member_status_id] := 1;   status_deltas[OLD.member_status_id] := -1;END IF;/*and after a couple more such conditional assignments I use thevalues in status_deltas to update another table holding status totals here*/
END;$um$ LANGUAGE plpgsql;on the two lines that access set array values I'm getting the followingerror:ERROR:  invalid array subscriptsWhat gives?What values are being used for member_status_id? 

Re: [SQL] Problem with array subscripts in plpgsql trigger function

[Erik Jones]

Aaron Bono wrote:

On 7/5/06, *Erik Jones* <[EMAIL PROTECTED] > wrote:

Ok, I have a trigger set up on the following (stripped down) table:

CREATE TABLE members (
member_id   bigint,
member_status_id   smallint,
member_is_deletedboolean
);

Here's a shortened version of the trigger function:

CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER AS $um$
DECLARE
status_deltas integer[];
BEGIN
IF(NEW.member_status_id != OLD.member_status_id AND
NEW.member_is_deleted IS NOT TRUE) THEN
   status_deltas[NEW.member_status_id] := 1;
   status_deltas[OLD.member_status_id] := -1;
END IF;
/*and after a couple more such conditional assignments I use the
values in status_deltas to update another table holding status
totals here*/
END;
$um$ LANGUAGE plpgsql;

on the two lines that access set array values I'm getting the
following
error:

ERROR:  invalid array subscripts

What gives?



What values are being used for member_status_id? 


1,  2, and 3

--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


---(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

Re: [SQL] Problem with array subscripts in plpgsql trigger function

[Aaron Bono]

On 7/5/06, Erik Jones <[EMAIL PROTECTED]> wrote:
Aaron Bono wrote:> On 7/5/06, *Erik Jones* <[EMAIL PROTECTED] [EMAIL PROTECTED]>> wrote:>> Ok, I have a trigger set up on the following (stripped down) table:
>> CREATE TABLE members (> member_id   bigint,> member_status_id   smallint,> member_is_deletedboolean> );>> Here's a shortened version of the trigger function:
>> CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER AS $um$> DECLARE> status_deltas integer[];> BEGIN> IF(NEW.member_status_id != OLD.member_status_id
 AND> NEW.member_is_deleted IS NOT TRUE) THEN>status_deltas[NEW.member_status_id] := 1;>status_deltas[OLD.member_status_id] := -1;> END IF;> /*and after a couple more such conditional assignments I use the
> values in status_deltas to update another table holding status> totals here*/> END;> $um$ LANGUAGE plpgsql;>> on the two lines that access set array values I'm getting the
> following> error:>> ERROR:  invalid array subscripts>> What gives? What values are being used for member_status_id?>1,  2, and 3
I did some digging through the documentation and cannot find any examples of using arrays like this.  Do you have to initialize the array before you use it?Does anyone know where to look for informaiton about using arrays in stored procedures?
-Aaron 

Re: [SQL] "CASE" is not a variable

[Keith Worthington]

On Wed, 28 Jun 2006 10:48:31 -0700, Bricklen Anderson wrote
> Keith Worthington wrote:
> >>> "Keith Worthington" <[EMAIL PROTECTED]> writes:
> >>> The following is a section of code inside an SQL function.
> >> On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote
> >> SQL, or plpgsql?  It looks to me like misuse of the plpgsql INTO clause
> >> (there can be only one).
> >>
> >>regards, tom lane
> > 
> > plpgsql
> > 
> > This is part of a function inside a v8.0.2 database.
> > 
> > I didn't realize that the INTO clause was only limited to one instance.  I 
> > was
> > trying to accomplish
> > 
> > SELECT col_a INTO var1,
> >col_b INTO var2,
> >col_c INTO var3,
> >...
> >   FROM foo
> >  WHERE fobar;
> > 
> > Kind Regards,
> > Keith
> 
> try it like
> select col_a,col_b,col_c INTO va1,var2,var3 
> 
> not sure if 8.0.2 allows you to do that, however.

Apparently it does.  Just to wrap up this thread and hopefully help the next
person.  When SELECTing multiple values into variables only use one instance of
the INTO clause.  i.e.

SELECT col_a,
   col_b,
   col_c
  INTO var1,
   var2,
   var3
  FROM foo
 WHERE fobar;

Kind Regards,
Keith

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Problem with array subscripts in plpgsql trigger function

[Erik Jones]

Aaron Bono wrote:

On 7/5/06, *Erik Jones* <[EMAIL PROTECTED] > wrote:

Aaron Bono wrote:
> On 7/5/06, *Erik Jones* <[EMAIL PROTECTED]
 >> wrote:
>
> Ok, I have a trigger set up on the following (stripped down)
table:
>
> CREATE TABLE members (
> member_id   bigint,
> member_status_id   smallint,
> member_is_deletedboolean
> );
>
> Here's a shortened version of the trigger function:
>
> CREATE OR REPLACE FUNCTION update_member() RETURNS TRIGGER
AS $um$
> DECLARE
> status_deltas integer[];
> BEGIN
> IF(NEW.member_status_id != OLD.member_status_id AND
> NEW.member_is_deleted IS NOT TRUE) THEN
>status_deltas[NEW.member_status_id] := 1;
>status_deltas[OLD.member_status_id] := -1;
> END IF;
> /*and after a couple more such conditional assignments I
use the
> values in status_deltas to update another table holding status
> totals here*/
> END;
> $um$ LANGUAGE plpgsql;
>
> on the two lines that access set array values I'm getting the
> following
> error:
>
> ERROR:  invalid array subscripts
>
> What gives?
>
>
>
> What values are being used for member_status_id?
>
1,  2, and 3 



I did some digging through the documentation and cannot find any 
examples of using arrays like this.  Do you have to initialize the 
array before you use it?


Does anyone know where to look for informaiton about using arrays in 
stored procedures?


-Aaron

Ok, that was apparently it.  I found href="http://archives.postgresql.org/pgsql-general/2005-02/msg01270.php";>this 
thread in the archive which indicated to me that without first 
initializing the array the bounds weren't set and thus a subscript error 
if each new index isn't sequential.


--
erik jones <[EMAIL PROTECTED]>
software development
emma(r)


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Foreign Key: what value? -- currval()

[Davi Leal]

A. Kretschmer wrote:
> Davi Leal folgendes:
> > Hi,
> > How to know the value which I must set in the foreign key field?. I have
> > two tables:
> >
> >
> > CREATE TABLE AA (
> >   Id  SERIAL PRIMARY KEY,
> >   data char(9)
> > );
> >
> > CREATE TABLE BB (
> >   BB_Id integer REFERENCES AA(Id) NOT NULL,
> >   field char(5)
> > );
> >
> >
> >
> >
> > I insert a register on table AA,
> >
> >INSERT INTO AA (data) VALUES ('123456789');
> >
> >
> > and then, I want to insert a related register in table BB, but I do
> > not know how get the proper value to the Foreign key BB_Id. Note that
> > a lot of
>
> currval() is your friend:
> 17:33 < akretschmer> ??currval
> 17:33 < pg_docbot_ads> For information about 'currval' see:
> 17:33 < pg_docbot_ads>
> http://www.postgresql.org/docs/current/static/functions-sequence.html

It works.
Thanks Andreas

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[SQL] week ending

[Keith Worthington]

Hi All,

I just finished writing a query that groups data based on the week number.

SELECT EXTRACT(week FROM col_a) AS week_number,
   sum(col_b) AS col_b_total
  FROM foo
 WHERE foobar
 GROUP BY EXTRACT(week FROM col_a)
 ORDER BY EXTRACT(week FROM col_a);

I would like to generate the starting date or ending date based on this 
number.  IOW instead of telling the user "week number" which they won't 
understand I would like to provide either Friday's date for "week 
ending" or Monday's date for "week beginning".


SELECT  AS week_ending,
   sum(col_b) AS col_b_total
  FROM foo
 WHERE foobar
 GROUP BY EXTRACT(week FROM col_a)
 ORDER BY EXTRACT(week FROM col_a);

--

Kind Regards,
Keith

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly