Hi,
I would like to write a function that can take in a table name and a pk value and make a copy of that row into the same table, but with a new value for the PK.
I originally had a working function that would create a temp table like the passed-in table, including defaults, and I found the name of the pk column and set that column to default. Since we use nextval('...') as the default value for our PKs, it would assign the new PK before we copied the row back into the table.
However, we found that cloning ~2000 rows in one transaction did not work, because creating and dropping the temp table once for each cloned row caused postgres to run out of shared memory and abort the transaction (it said I might have to increase max_locks_per_transaction, but I didn't understand why).
So, I decided to try to write it using record variables instead, to see if that would fix the problem. Below is the function I came up with, but it does not work because the record variable my_row cannot be accessed from within the EXECUTE.
How can I modify my function to assign the default value of the PK column into the corresponding column of the my_row record variable, and then insert the contents of that record back into the original table?
Alternatively, how can I keep Postgres from running out of memory with the temp table method?
The function:
CREATE OR REPLACE FUNCTION public.fn_clone_row(in_table_name character varying, in_row_pk integer)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
my_table_pk_col_name varchar;
my_row record;
my_pk_default text;
BEGIN
-- This is a function we've written and tested already
my_table_pk_col_name := fn_get_table_pk_col(in_table_name);
-- Get the next value of the pk column for the table
EXECUTE 'SELECT pa.adsrc '
' FROM pg_attrdef pa '
' JOIN pg_attribute pat '
' ON pat.attnum = pa.adnum '
' AND pat.attrelid = pa.adrelid '
' JOIN pg_class pc '
' ON pc.oid = pat.attrelid '
' JOIN pg_namespace pn '
' ON pn.oid = pc.relnamespace '
' WHERE pat.attname = ' || quote_nullable( my_table_pk_col_name ) ||
' AND pc.relname = ' || quote_nullable( in_table_name ) ||
' AND pn.nspname = ''public'''
INTO my_pk_default;
-- Copy over only the given row to the temp table.
EXECUTE ' SELECT * '
' FROM ' || quote_ident( in_table_name ) ||
' WHERE ' || quote_ident( my_table_pk_col_name ) || ' = '
|| quote_nullable( in_row_pk )
INTO my_row;
EXECUTE 'my_row.' || my_key || ' := ' || my_pk_default;
-- Copy the created row back into the original table.
EXECUTE ' INSERT INTO ' || quote_ident( in_table_name ) ||
' SELECT my_row.* ';
RETURN my_pk_default;
END
$function$
Thank you!
--
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@xxxxxxxxxxxx | www.neadwerx.com
moshe@xxxxxxxxxxxx | www.neadwerx.com
"Quality is not an act, it is a habit." -- Aristotle
