Automatic insert statement generator?

Поиск
Список
Период
Сортировка
От Rob Richardson
Тема Automatic insert statement generator?
Дата
Msg-id 04A6DB42D2BA534FAC77B90562A6A03DB921B3@server.rad-con.local
обсуждение исходный текст
Ответ на Re: Q: inheritance  ("Richard Broersma" <richard.broersma@gmail.com>)
Ответы Resp.: Automatic insert statement generator?
Список pgsql-general
Greetings!

I was going to make this a question, but I poked around a bit and came
up with an answer, which I'll share here in case anyone else is
interested.

I occasionally need to add test records to a database table.  For
example, I want a new charge that is identical to charge 18000, so I
need coils in inventory that match those in charge 18000:

insert into inventory
select * from inventory where charge = 18000

The problem, of course, is that the inventory table has a unique key
constraint that gets violated.  So, to do this, I'm going to have to
write an insert query that lists every field in this table (all 62 of
them), except for the primary key, which I'll have to force to something
I know is unique.  I would like a database function that would generate
a string that would be a concatenation of all fields in a given table.
Then, I could use the resulting string as the starting point for
building an insert statement that will avoid the key field(s).

So, if I have a table named 'small_table' that contains columns
'column1', 'column2' and 'column3', I would be able to execute:
    SELECT get_fields('small_table')
And I would get back:
    'column1, column2, column3'.

Here's what I did:

-- Function: list_fields("varchar")

-- DROP FUNCTION list_fields("varchar")

CREATE OR REPLACE FUNCTION list_fields("varchar")
  RETURNS "varchar" AS
$BODY$
declare
    Tablename ALIAS for $1;
    Attributes record;
    Result varchar;
begin
    Result := '';
    FOR Attributes IN SELECT attname FROM pg_attribute
              where attrelid = (select oid from pg_class where
relname = Tablename)
              and attstattarget <> 0
    LOOP
    if length(Result) <> 0 then
        Result = Result || ', ';
    end if;
    Result = Result || Attributes.attname;
    END LOOP;

    raise notice '%', Result;
    return Result;

 end;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION list_fields("varchar") OWNER TO caps;
GRANT EXECUTE ON FUNCTION list_fields("varchar") TO caps;
GRANT EXECUTE ON FUNCTION list_fields("varchar") TO public;


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

Предыдущее
От: Gerhard Heift
Дата:
Сообщение: Re: cumulative count
Следующее
От: Raymond O'Donnell
Дата:
Сообщение: Re: serial