From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Maximilian Tyrtania" <maximilian(dot)tyrtania(at)onlinehome(dot)de> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Select default values |
Date: | 2008-07-23 11:26:12 |
Message-ID: | 162867790807230426t71dd5910ye7b98945c0e3794@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
2008/7/23 Maximilian Tyrtania <maximilian(dot)tyrtania(at)onlinehome(dot)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 1
b | 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 | 1
b | 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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Maximilian Tyrtania | 2008-07-23 12:06:02 | Re: Select default values |
Previous Message | Karsten Hilbert | 2008-07-23 10:44:03 | Re: Select default values |