Re: Select default values

Pavel,

fantastic, that's exactly what I wanted, thank you very much!

Maximilian Tyrtania

> Von: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>

>> 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;