Re: Update more than one table
От | David Pratt |
---|---|
Тема | Re: Update more than one table |
Дата | |
Msg-id | D2BA5C0C-F2F9-11D9-97E3-000A27B3B070@eastlink.ca обсуждение исходный текст |
Ответ на | Re: Update more than one table (Bruno Wolff III <bruno@wolff.to>) |
Список | pgsql-general |
Hi Bruno and Roman. I am attempting to implement your advice. Bruno, how do I make a foreign key deferable since this sounds like an interesting approach. I have got another problem on top of the first. For the first two inserts I need to insert a multi-dimensional array into one of the fields of the table, and the order of the arrays within the larger array is important. So I am in the process of making a function that will insert the record into first table, rewrite the array and return currval. So problem I have run into is passing multi-dimensional array as a parameter for a function. To do a basic test of passing an array into a function I did this: CREATE FUNCTION create_record_test(text[][]) RETURNS int4 AS ' DECLARE test_array ALIAS FOR $1; -- alias for input array BEGIN return array_upper(test_array,1) END; ' LANGUAGE 'plpgsql'; SELECT create_record_test(ARRAY[ARRAY['A','test one'],ARRAY['B','test two']]) AS output; but I am getting syntax errors and I tried a variety of ways to quote the SELECT string and can't seem to get it take the array as an input :( Given the fact I will now have a function returning the currval for each insert (once I determine to pass array to function ), will the approaches suggested still work or should I create another function for doing the update for table 2 inserting currval each time as variable for select statement in the function and have function for insert in table 2 return currval as well? Regards, David On Tuesday, July 12, 2005, at 12:08 PM, Bruno Wolff III wrote: > On Sun, Jul 10, 2005 at 15:05:30 -0300, > David Pratt <fairwinds@eastlink.ca> wrote: >> Hi Roman. Many thanks for your reply. This is interesting and will I >> give this a try and let you know how it works out. With this you are >> right, application logic and transaction don't have to be separate >> which would be nice for this. I was thinking the only way to solve >> was >> a function that performed an update and returned the nextval at the >> same time so that I could use that value to perform the update on next >> table,etc. > > Normally you can just use currval. But in your case you insert insert > two > records and currval will only return the value of the second record's > key. > Assuming the first record's key is one less than the second's is not a > good > idea. With the current version you can probably make this work reliably > by grabbing a block of ids for your session and making sure that the > two > records get their keys from the same preallocated block. > > Another option that I think could work is to make the two foreign key > checks > deferrable and insert the record for table 2 before the two records in > table 1. You can use nextval(pg_get_serial_sequence('table1', > 'table1key')) > twice in the insert. Then when inserting the two entries into table 1 > you > can use currval to get the key value for the record in table 2 and use > the > appropiate column for each of the two records. As long as you aren't > depending on the ordering of the key values for the two records in > table 1 > you should be OK. > > ---------------------------(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 >
В списке pgsql-general по дате отправления: