Resp.: Automatic insert statement generator?

2008/12/4, Rob Richardson <Rob(dot)Richardson(at)rad-con(dot)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