Re: Update more than one table

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(at)eastlink(dot)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(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>