Thread: Can I get the default value for an attribute (field) ?
I'm building a GUI for a PostgreSQL database. In the DB, many fields have default values (a few are complicated, like the results of a sequence, but most are simple things like FALSE or 0 or such.) Is there a way to get what the default value for a field would be when a new record is added? For some tables, I could (behind the users back) add a row, grab the values given in each column, delete the row, then present these as 'default' values; however, in most tables, there are columns that cannot be null and do not have default values, therefore I can't just add a row using the default-only values. I've been looking around for a miraculous SELECT default_value(table.class) function, but can't seem to find it. Any ideas? -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
> I'm building a GUI for a PostgreSQL database. In the DB, many fields have > default values (a few are complicated, like the results of a sequence, but > most are simple things like FALSE or 0 or such.) > > Is there a way to get what the default value for a field would be when a > new record is added? For some tables, I could (behind the users back) add > a row, grab the values given in each column, delete the row, then > present these as 'default' values; however, in most tables, there are > columns that cannot be null and do not have default values, therefore I > can't just add a row using the default-only values. > > I've been looking around for a miraculous SELECT > default_value(table.class) function, but can't seem to find it. Any ideas? I should have mentioned that I know the default values are stored in pg_attrdef, in 'human' mode at adsrc, and in 'pg' mode at adbin; I could look there, but don't know how to 'evaluate' these to the real-world equivalents (ie, instead of finding 'f' or FALSE in a column, I get 'f'::bool, and by the time this gets to the GUI app and back again, it looks like '''f''::bool' to PG, so it sees it as text, not as the boolean value false. Similarly, "current_user"() isn't resolved, etc. So, one solution might be: is there a way to 'resolve' these before they come to the front-end? Thanks! -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
If you start psql with the -E option you'll see it generates a query similar to the following: SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c WHERE c.relname = 'my_table' AND c.oid = d.adrelid NAD d.adnum = X where my_table is the table in question and X is the column number within that table. Hope that helps, Mike Mascari mascarm@mascari.com -----Original Message----- From: Joel Burton [SMTP:jburton@scw.org] Sent: Sunday, April 01, 2001 5:25 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Can I get the default value for an attribute (field) ? I'm building a GUI for a PostgreSQL database. In the DB, many fields have default values (a few are complicated, like the results of a sequence, but most are simple things like FALSE or 0 or such.) Is there a way to get what the default value for a field would be when a new record is added? For some tables, I could (behind the users back) add a row, grab the values given in each column, delete the row, then present these as 'default' values; however, in most tables, there are columns that cannot be null and do not have default values, therefore I can't just add a row using the default-only values. I've been looking around for a miraculous SELECT default_value(table.class) function, but can't seem to find it. Any ideas? -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
On Sun, 1 Apr 2001, Joel Burton wrote:
> I should have mentioned that I know the default values are stored in
> pg_attrdef, in 'human' mode at adsrc, and in 'pg' mode at adbin; I could
> look there, but don't know how to 'evaluate' these to the real-world
> equivalents (ie, instead of finding 'f' or FALSE in a column, I get
> 'f'::bool, and by the time this gets to the GUI app and back again, it
> looks like '''f''::bool' to PG, so it sees it as text, not as the boolean
> value false. Similarly, "current_user"() isn't resolved, etc.
>
> So, one solution might be: is there a way to 'resolve' these before they
> come to the front-end?
Thanks to some help, I have a (working but hackish) solution:
1) I'll need to be able to turn bools into text. So:
CREATE FUNCTION text(bool) RETURNS text AS
'SELECT CASE WHEN TRUE THEN -1 ELSE 0 END;'
LANGUAGE 'sql' WITH (ISCACHABLE);
[ I'm building a front-end in Access, where the numeric
representation for truth=-1 and false=0, hence the values
here. ]
2) I need a way to 'evaluate' a PostgreSQL expression, such as
''foo'':text, 'f'::bool, "current_user"(), etc.
CREATE FUNCTION eval(text) RETURNS text AS '
DECLARE
r record;
q text;
BEGIN
q := ''SELECT ('' || $1 || ')::text AS a'';
FOR r IN EXECUTE q
LOOP
RETURN r.a
END LOOP;
END;
' LANGUAGE 'plpgsql';
3) I need a query to find the human-readable expression for each
column, and show me the evaluated version:
SELECT a.attname,
eval(d.adsrc)
FROM pg_class c,
pg_attribute a,
pg_attrdef d
WHERE c.oid = d.adrelid
AND d.adnum = a.attnum
AND a.attrelid = c.oid
AND c.relname = 'your_table_name'
ORDER BY a.attnum;
Seems to work.
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington
Joel Burton <jburton@scw.org> writes:
> Is there a way to get what the default value for a field would be when a
> new record is added?
pg_attrdef is it. Sorry it's not pretty enough for you ...
regards, tom lane
Mike Mascari wrote:
> If you start psql with the -E option you'll see it generates a query
> similar to the following:
>
> SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c
> WHERE c.relname = 'my_table' AND c.oid = d.adrelid NAD d.adnum = X
>
> where my_table is the table in question and X is the column number
> within that table.
Is there a way to do it in the properly relational way, I mean, using
attribute (column) name instead of a number?
--
_
/ \ Leandro Guimarães Faria Corsetti Dutra +55 (11) 3040 8913
\ / Amdocs at Bell Canada +1 (514) 786 87 47
X Support Center, São Paulo, Brazil mailto:adbaamd@bell.ca
/ \ http://terravista.pt./Enseada/1989/ mailto:leandrod@amdocs.com