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;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
