Thread: Select default values
Hi there, just a quickie: Is there a way to select all default values of a given table? Something like "Select Default values from sometable" ? Unfortunately this syntax doesn't seem to be supported. I know i can select the default values for each column, but being able to select them in one go would be handy... tia, Maximilian Tyrtania
am Wed, dem 23.07.2008, um 10:32:58 +0200 mailte Maximilian Tyrtania folgendes: > Hi there, just a quickie: Is there a way to select all default values of a > given table? Something like "Select Default values from sometable" ? > Unfortunately this syntax doesn't seem to be supported. I know i can select > the default values for each column, but being able to select them in one go > would be handy... test=# create table t_with_defaults( s1 int default 1, s2 int default 2); CREATE TABLE test=*# select ordinal_position, column_name, column_default from information_schema.columns where table_name='t_with_defaults'order by 1;ordinal_position | column_name | column_default ------------------+-------------+---------------- 1 | s1 | 1 2 | s2 | 2 (2 rows) HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hello 2008/7/23 Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>: > Hi there, just a quickie: Is there a way to select all default values of a > given table? Something like "Select Default values from sometable" ? > Unfortunately this syntax doesn't seem to be supported. I know i can select > the default values for each column, but being able to select them in one go > would be handy... it's not possible directly, you can find expressions used as default in system tables or postgres=# create table f(a integer default 1, b integer); CREATE TABLE postgres=# insert into f(a,b) values(default, default) returning *;a | b ---+---1 | (1 row) INSERT 0 1 regards Pavel Stehule > > tia, > > Maximilian Tyrtania > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Hi again, >> Hi there, just a quickie: Is there a way to select all default values of a >> given table? Something like "Select Default values from sometable" ? > it's not possible directly, you can find expressions used as default > in system tables or > postgres=# create table f(a integer default 1, b integer); > CREATE TABLE > postgres=# insert into f(a,b) values(default, default) returning *; > a | b > ---+--- > 1 | > (1 row) > > INSERT 0 1 > regards > Pavel Stehule ah, I see, smart, the problem with this is just that I don't actually want to insert the record just yet. I just want to hand the default values over to my app. Well, of course my app could still delete the record later on, but still that seems cumbersome.. Thanks a lot and best wishes from Berlin, Maximilian Tyrtania
Hi,
> am Wed, dem 23.07.2008, um 10:32:58 +0200 mailte Maximilian Tyrtania
> folgendes:
>> Hi there, just a quickie: Is there a way to select all default values of a
>> given table? Something like "Select Default values from sometable" ?
>
> test=# create table t_with_defaults( s1 int default 1, s2 int default 2);
> CREATE TABLE
> test=*# select ordinal_position, column_name, column_default from
> information_schema.columns where table_name='t_with_defaults' order by 1;
> ordinal_position | column_name | column_default
> ------------------+-------------+----------------
> 1 | s1 | 1
> 2 | s2 | 2
> (2 rows)
This is probably what I should do, the only problem is that the output of
the given query looks a lot less nice when the default looks like this
nextval('mitarbeiter_serial'::regclass)
I'd prefer to just receive the actual value of that function. Okay, I could
just execute that statement, but, hmm, still, that seems akward.
Thanks and best wishes from Berlin
Maximilian Tyrtania
2008/7/23 Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>: > Hi again, > >>> Hi there, just a quickie: Is there a way to select all default values of a >>> given table? Something like "Select Default values from sometable" ? > >> it's not possible directly, you can find expressions used as default >> in system tables or >> postgres=# create table f(a integer default 1, b integer); >> CREATE TABLE >> postgres=# insert into f(a,b) values(default, default) returning *; >> a | b >> ---+--- >> 1 | >> (1 row) >> >> INSERT 0 1 >> regards >> Pavel Stehule > > ah, I see, smart, the problem with this is just that I don't actually want > to insert the record just yet. I just want to hand the default values over > to my app. Well, of course my app could still delete the record later on, > but still that seems cumbersome.. begin insert ...rollback; it's not best solution, but it just works. regards Pavel > > Thanks a lot and best wishes from Berlin, > > Maximilian Tyrtania > > >
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). 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
On Wed, Jul 23, 2008 at 12:35:08PM +0200, Maximilian Tyrtania wrote: > With your way (insert into f(a,b) values(default, default) returning *) i > need to know everything about the given table. > > Hmm. Any ideas? Do look at the information schema. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
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
>
>
>
Pavel,
fantastic, that's exactly what I wanted, thank you very much!
Maximilian Tyrtania
> Von: Pavel Stehule <pavel.stehule@gmail.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;
On Wed, Jul 23, 2008 at 3:57 AM, Maximilian Tyrtania
<maximilian.tyrtania@onlinehome.de> wrote:
> Hi,
>
>> am Wed, dem 23.07.2008, um 10:32:58 +0200 mailte Maximilian Tyrtania
>> folgendes:
>>> Hi there, just a quickie: Is there a way to select all default values of a
>>> given table? Something like "Select Default values from sometable" ?
>>
>> test=# create table t_with_defaults( s1 int default 1, s2 int default 2);
>> CREATE TABLE
>> test=*# select ordinal_position, column_name, column_default from
>> information_schema.columns where table_name='t_with_defaults' order by 1;
>> ordinal_position | column_name | column_default
>> ------------------+-------------+----------------
>> 1 | s1 | 1
>> 2 | s2 | 2
>> (2 rows)
>
> This is probably what I should do, the only problem is that the output of
> the given query looks a lot less nice when the default looks like this
>
> nextval('mitarbeiter_serial'::regclass)
>
> I'd prefer to just receive the actual value of that function. Okay, I could
> just execute that statement, but, hmm, still, that seems akward.
Until you run that function, you don't know what the output might be
because of possible race condtitions.
On 23/lug/08, at 11:28, Pavel Stehule wrote: > Hello > > 2008/7/23 Maximilian Tyrtania <maximilian.tyrtania@onlinehome.de>: >> Hi there, just a quickie: Is there a way to select all default >> values of a >> given table? Something like "Select Default values from sometable" ? >> Unfortunately this syntax doesn't seem to be supported. I know i >> can select >> the default values for each column, but being able to select them >> in one go >> would be handy... > > > it's not possible directly, you can find expressions used as default > in system tables or > postgres=# create table f(a integer default 1, b integer); > CREATE TABLE > postgres=# insert into f(a,b) values(default, default) returning *; It seems that you can’t use the same syntax with function calls: select function(default,default); gives a syntax error. Is it expected? -- Giorgio Valoti
Giorgio Valoti wrote: > >> postgres=# insert into f(a,b) values(default, default) returning *; > > It seems that you can’t use the same syntax with function calls: > select function(default,default); > gives a syntax error. Is it expected? Um - there is no default value for a function. -- Richard Huxton Archonet Ltd
On 24/lug/08, at 12:42, Richard Huxton wrote: > Giorgio Valoti wrote: >>> postgres=# insert into f(a,b) values(default, default) returning *; >> It seems that you can’t use the same syntax with function calls: >> select function(default,default); >> gives a syntax error. Is it expected? > > Um - there is no default value for a function. Yes, but you could define a domain with a default value and using it as an IN argument for a function. In that case it would handy to be able to use the default value, wouldn’t it? Without this "feature" you have to overload the function arguments. -- Giorgio Valoti
On Thu, Jul 24, 2008 at 12:35 PM, Giorgio Valoti <giorgio_v@mac.com> wrote: >> Um - there is no default value for a function. > > Without this "feature" you have to overload > the function arguments. You could pass a casted null to the function. The would eliminate function overloading. Then internally you could handle the null by passing DEFAULTS to you INSERT or UPDATE statements. I don't know if this would work for you in this case. -- Regards, Richard Broersma Jr. Visit the Los Angles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On 24/lug/08, at 23:15, Richard Broersma wrote: > On Thu, Jul 24, 2008 at 12:35 PM, Giorgio Valoti > <giorgio_v@mac.com> wrote: > >>> Um - there is no default value for a function. >> >> Without this "feature" you have to overload >> the function arguments. > > You could pass a casted null to the function. The would eliminate > function overloading. Then internally you could handle the null by > passing DEFAULTS to you INSERT or UPDATE statements. I don't know if > this would work for you in this case. It could work but only if I use a domain that allows NULLs, which reduces the usefulness of domains even if you can work around that by simply stating the not null clause in the table definition. -- Giorgio Valoti