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?

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