Re: Select default values
От | Pavel Stehule |
---|---|
Тема | Re: Select default values |
Дата | |
Msg-id | 162867790807230426t71dd5910ye7b98945c0e3794@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Select default values (Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>) |
Ответы |
Re: Select default values
(Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>)
|
Список | pgsql-sql |
2008/7/23 Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>: > Hi, > >> begin >> insert ... >> rollback; >> >> it's not best solution, but it just works. > > Ah, yes, of course, haven't thought of that. > > Okay, here is one final (i hope) obstacle. My db has >200 tables and I'd > love to be able to write some function that would just take a tablename and > return the default values for a new record of that table. If "Select default > values from sometable" was supported than that would be a piece of cake (I'd > just do: Execute "Select default values from '||sometable||' into > somerecord" in a plpgsql function). > that is out of SQL principles :(. And you cannot have functions that returns different number of columns - your function, can return array or table CREATE OR REPLACE FUNCTION defaults(text, OUT attname name, OUT type varchar, OUT default_val varchar) RETURNS SETOF RECORD AS $$ SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT eval(pg_catalog.pg_get_expr(d.adbin, d.adrelid)) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) FROM pg_catalog.pg_attribute a WHERE a.attrelid = $1::regclass::oid AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum $$ LANGUAGE SQL STRICT; postgres=# \d fg Table "public.fg"Column | Type | Modifiers --------+------+-----------------------------t | date | default ('now'::text)::date postgres=# \d f Table "public.f"Column | Type | Modifiers --------+---------+-----------a | integer | default 1b | integer | postgres=# select * from defaults('fg');attname | type | default_val ---------+------+-------------t | date | 2008-07-23 (1 row) postgres=# select * from defaults('f');attname | type | default_val ---------+---------+-------------a | integer | 1b | integer | (2 rows) regards Pavel Stehule create or replace function eval(varchar) returns varchar as $$ declare result varchar; begin execute 'SELECT ' || $1 into result; return result; end;$$ language plpgsql strict; > With your way (insert into f(a,b) values(default, default) returning *) i > need to know everything about the given table. > > Hmm. Any ideas? > > Best, > > Maximilian Tyrtania > > >
В списке pgsql-sql по дате отправления: