•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 397,577 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,383 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser:
Views: 884 | Replies: 6
 |
•
•
Join Date: Aug 2006
Location: South Africa, Durban
Posts: 100
Reputation: 
Rep Power: 3
Solved Threads: 8
Junior Poster
SQL Help 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
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.
Table Observation
Time_Stamp - primary key
...
Observation_ID - foreign keyTable 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.
Last edited by PoovenM : Dec 7th, 2007 at 8:21 am.
•
•
Join Date: Aug 2006
Location: South Africa, Durban
Posts: 100
Reputation:
Rep Power: 3
Solved Threads: 8
Junior Poster
So creating a Trigger for PostGres isn't as easy as I thought it would be. I would have used to following query:
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:
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:
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.
sql Syntax (Toggle Plain Text)
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:
sql Syntax (Toggle Plain Text)
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.
•
•
Join Date: Sep 2007
Location: North Bay Ontario
Posts: 174
Reputation:
Rep Power: 1
Solved Threads: 20
Junior Poster
•
•
•
•
Originally Posted by PoovenM 
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.
Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
"Others make web sites. We make web sites work!"
•
•
Join Date: Aug 2006
Location: South Africa, Durban
Posts: 100
Reputation:
Rep Power: 3
Solved Threads: 8
Junior Poster
Well I searched for PostGres Forums (plus I don't like joining forums - I only like DaniWeb 
) 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.
) 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. Last edited by PoovenM : Dec 10th, 2007 at 1:09 am.
•
•
Join Date: Sep 2007
Location: North Bay Ontario
Posts: 174
Reputation:
Rep Power: 1
Solved Threads: 20
Junior Poster
•
•
•
•
Originally Posted by PoovenM
Well I searched for PostGres Forums (plus I don't like joining forums - I only like DaniWeb
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. Amer Neely - Web Mechanic
"Others make web sites. We make web sites work!"
"Others make web sites. We make web sites work!"
•
•
Join Date: Aug 2006
Location: South Africa, Durban
Posts: 100
Reputation:
Rep Power: 3
Solved Threads: 8
Junior Poster
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:
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:
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:
Hmmm maybe this post should be moved
but hope it helps someone out there 
•
•
•
•
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_seq')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:
sql Syntax (Toggle Plain Text)
Hmmm maybe this post should be moved
but hope it helps someone out there •
•
Join Date: Aug 2006
Location: South Africa, Durban
Posts: 100
Reputation:
Rep Power: 3
Solved Threads: 8
Junior Poster
Re: SQL Help Oops, one other thing:
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
... 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 
•
•
•
•
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
... 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 Last edited by PoovenM : Dec 10th, 2007 at 9:19 am.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
Linear Mode
Other Threads in the MySQL Forum
- Previous Thread: data ware housing
- Next Thread: Chicago Developer Needed
