[SQL] 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select default values
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select default values
Hello 2008/7/23 Maximilian Tyrtania <[EMAIL PROTECTED]>: > 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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select default values
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select default values
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select default values
2008/7/23 Maximilian Tyrtania <[EMAIL PROTECTED]>: > 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 > > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select default values
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select default values
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select default values
2008/7/23 Maximilian Tyrtania <[EMAIL PROTECTED]>:
> 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
>
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select default values
Pavel,
fantastic, that's exactly what I wanted, thank you very much!
Maximilian Tyrtania
> Von: Pavel Stehule <[EMAIL PROTECTED]>
>> 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;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Select default values
On Wed, Jul 23, 2008 at 3:57 AM, Maximilian Tyrtania
<[EMAIL PROTECTED]> 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.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Query prepared plan
Good morning, May I know the "commands" to . show current session's prepared plans . see the definition of a prepared plan E.g., psql> PREPARE fooplan (int, text, bool, numeric) AS INSERT INTO foo VALUES($1, $2, $3, $4); (1) Similar to "\dt", I want to see "fooplan" (2) Similar to "\d tableName", how to see the plan def? Thanks a lot! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
