Dynamically accessing record elements using EXECUTE

Поиск
Список
Период
Сортировка
От Moshe Jacobson
Тема Dynamically accessing record elements using EXECUTE
Дата
Msg-id CAJ4CxLmmef=sP8OZgPkfCEqNrvP9JY4U+Df-uYKN36AkuqLhOw@mail.gmail.com
обсуждение исходный текст
Список pgsql-general
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
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | 
www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle

В списке pgsql-general по дате отправления:

Предыдущее
От: Joe Van Dyk
Дата:
Сообщение: domains, case statements, functions: bug?
Следующее
От: Robert James
Дата:
Сообщение: Re: Longest Common Subsequence in Postgres - Algorithm Challenge