pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help with INSERT into 2 tables
6+ messages / 6 participants
[nested] [flat]

* Help with INSERT into 2 tables
@ 2001-11-13 13:42 Gintas <gntrs@hotmail.com>
  2001-11-15 15:11 ` Re: Help with INSERT into 2 tables Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-11-15 15:47 ` Re: Help with INSERT into 2 tables Jeff Eckermann <jeff_eckermann@yahoo.com>
  2001-11-15 16:44 ` Re: Help with INSERT into 2 tables Roland Roberts <roland@astrofoto.org>
  2001-11-16 06:13 ` Re: Help with INSERT into 2 tables Andrew G. Hammond <drew@xyzzy.dhs.org>
  0 siblings, 4 replies; 6+ messages in thread

From: gntrs@hotmail.com @ 2001-11-13 13:42 UTC (permalink / raw)
  To: pgsql-sql

I am new to SQL and don't know how to INSERT records to 2 tables.

The first table:

     CREATE TABLE aaaa ( aaaaid SERIAL PRIMARY KEY,
                         text VARCHAR(20)
                       );

Second table:

     CREATE TABLE bbbb ( bbbbid SERIAL PRIMARY KEY,
                         aaaaid INTEGER REFERENCES aaaa (aaaaid) ON
DELETE CASCADE,
                         sometext VARCHAR(200)
                       );

I want to insert related records to both table.  The problem is that
in order to insert record to the second table it's necessary to know
"aaaaid" field from the first table("text" is not UNIQUE):

INSERT INTO aaaa (text) VALUES ('Some info');
INSERT INTO bbbb (aaaaid, sometext) VALUES (?????, 'Some text');

How is it possible to do that?
(I am inserting this from JAVA).

Thanks for help.



Gintaras



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Help with INSERT into 2 tables
  2001-11-13 13:42 Help with INSERT into 2 tables Gintas <gntrs@hotmail.com>
@ 2001-11-15 15:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  3 siblings, 0 replies; 6+ messages in thread

From: Stephan Szabo @ 2001-11-15 15:11 UTC (permalink / raw)
  To: Gintas <gntrs@hotmail.com>; +Cc: pgsql-sql


On 13 Nov 2001, Gintas wrote:

> I am new to SQL and don't know how to INSERT records to 2 tables.
>
> The first table:
>
>      CREATE TABLE aaaa ( aaaaid SERIAL PRIMARY KEY,
>                          text VARCHAR(20)
>                        );
>
> Second table:
>
>      CREATE TABLE bbbb ( bbbbid SERIAL PRIMARY KEY,
>                          aaaaid INTEGER REFERENCES aaaa (aaaaid) ON
> DELETE CASCADE,
>                          sometext VARCHAR(200)
>                        );
>
> I want to insert related records to both table.  The problem is that
> in order to insert record to the second table it's necessary to know
> "aaaaid" field from the first table("text" is not UNIQUE):
>
> INSERT INTO aaaa (text) VALUES ('Some info');
> INSERT INTO bbbb (aaaaid, sometext) VALUES (?????, 'Some text');
I think you want:
 currval('aaaa_aaaaid_seq')





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Help with INSERT into 2 tables
  2001-11-13 13:42 Help with INSERT into 2 tables Gintas <gntrs@hotmail.com>
@ 2001-11-15 15:47 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
  3 siblings, 0 replies; 6+ messages in thread

From: Jeff Eckermann @ 2001-11-15 15:47 UTC (permalink / raw)
  To: Gintas <gntrs@hotmail.com>; pgsql-sql

You will need to:
1. Insert into aaaa
2. Get value of aaaaid for inserted record
3. Include aaaaid value explicitly in your insert to
bbbb.

The only question is how to do 2 above.  AFAIK doing a
separate command "SELECT
currval(aaaaid_sequence_name)" should work. 
Alternatively, if the oid of the last inserted record
is returned to your Java program (sorry, I don't know
anything about the JDBC driver functionality, so don't
know whether that is so or not), then you can do
"SELECT aaaaid FROM aaaa WHERE oid = <oid of inserted
record>": which would probably be safer.
--- Gintas <gntrs@hotmail.com> wrote:
> I am new to SQL and don't know how to INSERT records
> to 2 tables.
> 
> The first table:
> 
>      CREATE TABLE aaaa ( aaaaid SERIAL PRIMARY KEY,
>                          text VARCHAR(20)
>                        );
> 
> Second table:
> 
>      CREATE TABLE bbbb ( bbbbid SERIAL PRIMARY KEY,
>                          aaaaid INTEGER REFERENCES
> aaaa (aaaaid) ON
> DELETE CASCADE,
>                          sometext VARCHAR(200)
>                        );
> 
> I want to insert related records to both table.  The
> problem is that
> in order to insert record to the second table it's
> necessary to know
> "aaaaid" field from the first table("text" is not
> UNIQUE):
> 
> INSERT INTO aaaa (text) VALUES ('Some info');
> INSERT INTO bbbb (aaaaid, sometext) VALUES (?????,
> 'Some text');
> 
> How is it possible to do that?
> (I am inserting this from JAVA).
> 
> Thanks for help.
> 
> 
> 
> Gintaras
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


__________________________________________________
Do You Yahoo!?
Find the one for you at Yahoo! Personals
http://personals.yahoo.com



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Help with INSERT into 2 tables
  2001-11-13 13:42 Help with INSERT into 2 tables Gintas <gntrs@hotmail.com>
@ 2001-11-15 16:44 ` Roland Roberts <roland@astrofoto.org>
  3 siblings, 0 replies; 6+ messages in thread

From: Roland Roberts @ 2001-11-15 16:44 UTC (permalink / raw)
  To: pgsql-sql

>>>>> "Gintas" == Gintas  <gntrs@hotmail.com> writes:

    Gintas> I am new to SQL and don't know how to INSERT records to 2 tables.
    Gintas> The first table:

    Gintas>      CREATE TABLE aaaa ( aaaaid SERIAL PRIMARY KEY,
    Gintas>                          text VARCHAR(20)
    Gintas>                        );

    Gintas> Second table:

    Gintas>      CREATE TABLE bbbb ( bbbbid SERIAL PRIMARY KEY,
    Gintas>                          aaaaid INTEGER REFERENCES aaaa (aaaaid) ON
    Gintas> DELETE CASCADE,
    Gintas>                          sometext VARCHAR(200)
    Gintas>                        );

    Gintas> I want to insert related records to both table.  The
    Gintas> problem is that in order to insert record to the second
    Gintas> table it's necessary to know "aaaaid" field from the first
    Gintas> table("text" is not UNIQUE):

    Gintas> INSERT INTO aaaa (text) VALUES ('Some info');
    Gintas> INSERT INTO bbbb (aaaaid, sometext) VALUES (?????, 'Some text');

    Gintas> How is it possible to do that?  (I am inserting this from
    Gintas> JAVA).

The normal way to do this is to explicitly pull out the serial value
from the underlying sequence.  I.e., something like this

    select nextval('aaaa_aaaaid_seq');
    begin transaction;
    insert into aaaa (aaaaid, text) values ($seqno, 'Some info');
    insert into bbbb (aaaaid, sometext) VALUES ($seqno, 'Some text');
    end transaction;

where you use the jdbc calls to retrieve the result from the "select
nextval...".

SERIAL types are not quite as useful as people tend to think unless
you have the ability to identify the value from some other
characteristic.  If the ID is purely an ID and you don't care about
its value, you still have to have a way of *finding* it.

roland
-- 
		       PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD                             RL Enterprises
roland@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Help with INSERT into 2 tables
  2001-11-13 13:42 Help with INSERT into 2 tables Gintas <gntrs@hotmail.com>
@ 2001-11-16 06:13 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
  2001-11-16 16:47   ` Re: Help with INSERT into 2 tables Josh Berkus <josh@agliodbs.com>
  3 siblings, 1 reply; 6+ messages in thread

From: Andrew G. Hammond @ 2001-11-16 06:13 UTC (permalink / raw)
  To: gntrs@hotmail.com; pgsql-sql

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

CREATE TABLE a (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE b (id SERIAL PRIMARY KEY, 
	key_a INTEGER REFERENCES a (id) ON DELETE CASCADE,
	data TEXT);

> I want to insert related records to both table.  The problem is that
> in order to insert record to the second table it's necessary to know
> a.id

Common question, and there's several possible answers.  I'm going to break 
slightly from the norm though.  Postgres has some cool features, and since we 
can stuff this logic into the database, we might just as well.  So let's play 
with them!

CREATE VIEW ab AS SELECT a.id, a.name, b.data FROM a,b WHERE a.id = b.key_a;
CREATE FUNCTION ab_insert (text, text) RETURNS INTEGER AS '
	DECLARE newid INTEGER;
		newname ALIAS FOR $1;
		newdata ALIAS FOR $2;
	BEGIN	newid := nextval(''a_id_seq'');
		RAISE NOTICE ''newid is %'', newid;
		INSERT INTO a (id, name) VALUES (newid, newname);
		INSERT INTO b (key_a, data) VALUES (newid, newdata);
		RETURN newid;
	END;' LANGUAGE 'plpgsql';
CREATE RULE ab_ins_rule AS ON INSERT TO ab DO INSTEAD 
	SELECT ab_insert(new.name, new.data);

I'd really like to figure out how to write a function that doesn't return 
anything (a "procedure" for all you pedantic CS types).  Oh well.
- -- 
Andrew G. Hammond     mailto:drew@xyzzy.dhs.org   http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F                  613-389-5481
5CD3 62B0 254B DEB1 86E0  8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAjv0roYACgkQCT73CrRXhLFd4wCeNvUf1sYztKvs0Xqq9cfcDy97
n/wAmwXdCCaxrKQ6oTbtqSyhJ2IhSExG
=78uf
-----END PGP SIGNATURE-----



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Help with INSERT into 2 tables
  2001-11-13 13:42 Help with INSERT into 2 tables Gintas <gntrs@hotmail.com>
  2001-11-16 06:13 ` Re: Help with INSERT into 2 tables Andrew G. Hammond <drew@xyzzy.dhs.org>
@ 2001-11-16 16:47   ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Josh Berkus @ 2001-11-16 16:47 UTC (permalink / raw)
  To: Andrew G. Hammond <drew@xyzzy.dhs.org>; gntrs@hotmail.com; pgsql-sql

Andrew,

> I'd really like to figure out how to write a function that doesn't
> return 
> anything (a "procedure" for all you pedantic CS types).  Oh well.

Easy.  Try:
CREATE FUNCTION ... RETURNS OPAQUE AS '
All triggers are expected to return Opaque, that is, nothing.

FYI, A "procedure" in SQL92 terms is waht we in Postgres call a
Function, except that Stored Procedures can return one or more result
sets as well/instead of a value.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-11-16 16:47 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-13 13:42 Help with INSERT into 2 tables Gintas <gntrs@hotmail.com>
2001-11-15 15:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-11-15 15:47 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2001-11-15 16:44 ` Roland Roberts <roland@astrofoto.org>
2001-11-16 06:13 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
2001-11-16 16:47   ` Josh Berkus <josh@agliodbs.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox