Resp.: Automatic insert statement generator?

2008/12/4, Rob Richardson <Rob.Richardson@rad-con.com>:
> ...
> 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).
>

Try:
CREATE OR REPLACE FUNCTION list_fields(text) RETURNS text AS
$BODY$
-- all attributes names, except those belonging primary key
SELECT array_to_string(
         ARRAY(SELECT pa.attname FROM pg_attribute pa
                                 JOIN pg_class pc ON (pa.attrelid = pc.oid)
                WHERE pc.relname = $1
                  AND pa.attnum > 0
                  AND  pa.attnum <> ALL ((SELECT pco.conkey FROM
pg_constraint pco
                                           WHERE pco.conrelid = pa.attrelid
                                             AND pco.contype =
'p')::smallint[])),
                         ',');
$BODY$
LANGUAGE SQL STABLE;

Osvaldo