SQL Help

[PoovenM]

Hi all, I'm not sure how to phrase this question... I have a table that requires a foreign key of anything table - this foreign key is automatically generated and the key field in the 'foreign' table. So I have the following situation (kind of):

Table Observation
Time_Stamp - primary key
...
Observation_ID - foreign key

Table Observation_Value
Observation_ID - primary key
...

So obviously I need to insert into the table Observation_Value first before I can insert into table Observation, but how to I get the automatically generated foreign key?

I was thinking of using a trigger - when a value is inserted into Observation_Value then add the primary key to a new table (probably a view) and get that value for insertion into Observation then drop temporary table. I wanted to know if there's an easier way to do this? I can't change the table structure in any way either.... any help would be appreciated. I'm not even sure what type of search query I should use to find help.

[PoovenM]

So creating a Trigger for PostGres isn't as easy as I thought it would be. I would have used to following query:

--create view Observation_Key
CREATE VIEW Observation_Key
AS SELECT observation_id from Observation_value;
delete from Observation_key;

CREATE TRIGGER onObservationEntry
AFTER INSERT ON Observation_Key
FOR EACH STATEMENT
INSERT INTO Observation_Key VALUES (new.observation_id);

I thought that I could create a view in the same manner as a table, but I was mistaken - so I just create a view from the Obversation_Value table and deleted the contents of the view. Is there a more efficient way to achieve this? I just want a view with one field - an integer. Anyway my main problem is that the trigger code gives an error on the line: INSERT INTO Observation_Key VALUES (new.observation_id); It says syntax error at or near "INSERT". In fact it doesn't recognize the use of the keyword new.
I read that you have to use a function? So I tried this out:

CREATE VIEW Observation_Key
AS SELECT observation_id from Observation_value;
delete from Observation_key;
CREATE FUNCTION doInsert(id int)
AS 'INSERT INTO Observation_Key VALUES (id)';

CREATE TRIGGER onObservationEntry
AFTER INSERT ON Observation_Key
FOR EACH STATEMENT
EXECUTE PROCEDURE doInsert(new.observation_id);

And that's how I know it doesn't recognize the new keyword, because it says: syntax error at or near "new". I'm not even sure if my function will work? I just saw an example and imitated it. Can anyone help or direct me to documentation? The PostGres documentation didn't really clear up my confusing.

[trudge]

So creating a Trigger for PostGres isn't as easy as I thought it would be. I would have used to following query:

Umm, wouldn't you have a better chance at getting an answer if you posted in the proper forum or newsgroup? This one is for MySQL.

[PoovenM]

Well I searched for PostGres Forums (plus I don't like joining forums - I only like DaniWeb :icon_biggrin:) and I didn't find any good ones (is there a place in DaniWeb this post should go?) but perhaps I should check out a newsgroup - I've never really joined one before. I used this forum 'cause both MySQL and PostGres are OpenSource so I thought that maybe they would be almost alike :?: But my first question does stand... I'd like to be able to execute the above mentioned query without the use of triggers.

[trudge]

Well I searched for PostGres Forums (plus I don't like joining forums - I only like DaniWeb :icon_biggrin:) and I didn't find any good ones (is there a place in DaniWeb this post should go?) but perhaps I should check out a newsgroup - I've never really joined one before. I used this forum 'cause both MySQL and PostGres are OpenSource so I thought that maybe they would be almost alike :?: But my first question does stand... I'd like to be able to execute the above mentioned query without the use of triggers.

Newsgroups are usually my first choice for this kind of thing, but YMMV.

alt.comp.databases.postgresql

Triggers are a bit beyond my SQL sphere, but I suspect someone in that group should be able to help. Sorry that's my only input - hope you get an answer. If you do, could you post it back here as well for the rest of us :) Good luck.

[PoovenM]

Thanks for the tip. I actually joined the PostGres maillisting (pgsql-sql listing) and this guy (Andreas Kretschmer) helped me out - he's bloody brilliant! Here's what he told me:

First, I create two tables, master and slave. Master contains a
serial-field, this field is referenced by the slave table:

test=# create table master (id serial primary key, name text);
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(master_id int references master, val text);
CREATE TABLE

Now i have 2 tables and a sequence named 'master_id_seq', and now the
insert's:

test=*# insert into master (name) values ('test');
INSERT 0 1
test=*# insert into slave (master_id, val) values (currval('master_id_seq'), 'value');
INSERT 0 1

The parameter for currval() is the name of the sequence. Note: you have to
insert in the master table first, this calls the nextval() for the
sequence. After, within this database session, you can use currval() to
obtain the actual value for this sequence. And yes, this way is safe
also for concurrency.

Allow me to explain a bit more. There are a few functions that come with PostGres, currval() is one of them. He told me to use that function to which I replied: "I'm not really sure how to the currval() method. I've read up on it and I noticed it works with nextval() and setval(). The parameter for currval() is a regex - is there a regex to represent the most recently automatically generated number ( i.e. a serial field)?" Which gave me the above reply.

In short, in PostGres if you want the most recently auto-generated field from a given table you use: currval('tableName_fieldName_[B]seq[/B]') So you'd always append seq, tableName and fieldName change according to your table and field from that table (where that field should be of type serial).

An example of my query is:

INSERT INTO observation_value (phenomenon_id, value) VALUES ('urn:ogc:def:phenomenon:OGC:1.0.30:temperature', '15');
INSERT INTO observation VALUES ('2004-10-19 10:23:54', 'urn:ogc:def:procedure:wavertrend_rx201_sensory_web', 'COR01', currval('observation_value_observation_id_seq'));

Hmmm maybe this post should be moved :icon_confused: but hope it helps someone out there :icon_cheesygrin:

[PoovenM]

Oops, one other thing:

It's not a regex. I assume you are confused because it says "regclass". This is shorthand for "registered class" (where "class" is a synonymous for "relation", in this case a sequence).

That was left by Alvaro Herrera on the same PostGres maillisting. I was thinking regular expression 'cause I miss read but when I used it I was thinking relation name when I was using it. Well that's all folks :icon_smile: ... unless someone can help with solving this problem using pure SQL - without functions defined for particular database systems. And hey if you know how a trigger works in PostGres, I don't mind knowing it too ;)