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@xxxxxxxxxxx> 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@xxxxxxxxxxxxxx so that
your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
