Thread: get column default value

Jean-Christophe Roux wrote:
> I understand that but I need to build an interface so that my user can
> change the default values of some table/columns. So I need a way to
> query the current default value to show the user what it is currently.
> For instance, let's say I have a table products and there is a column
> price and the default price is $199, I'd like to get that $199 and
> give my user the opportunity to update that value. The column does not
> have any sequence attached to it.
> JCR
>
> ----- Original Message ----
> From: Ketema Harris <ketema@gmail.com>
> To: Jean-Christophe Roux <jcxxr@yahoo.com>
> Cc: pgsql-php@postgresql.org
> Sent: Monday, November 13, 2006 9:59:29 AM
> Subject: Re: [PHP] get column default value
>
> The default value for a column is just that the value you say is
> default.  It only come into play when you do an insert into the
> table.  If you do not provide a value for that column then your
> default value is entered.
>
> Now if you set the default value of a column to a sequence, then you
> can manage the sequence using the sequence functions: currval,
> nextval, and setval.  Sequences and their functions are documented in
> the pg manual.
>
> On Nov 13, 2006, at 9:49 AM, Jean-Christophe Roux wrote:
>
>> Hello,
>> How can I get the default value for a column?
>> To change the default value, something like
>> ALTER TABLE dummy ALTER COLUMN value SET DEFAULT -12;
>> would do the job, but how could I query the value?
>>
>>
>> Thanks in advance
>
SELECT adsrc as default_value
FROM pg_attrdef pad, pg_atttribute pat, pg_class pc
WHERE pc.relname='your_table_name'
    AND pc.oid=pat.attrelid AND pat.attname='your_column_name'
    AND pat.attrelid=pad.adrelid AND pat.attnum=pad.adnum

--
erik jones <erik@myemma.com>
software development
emma(r)

Jean-Christophe Roux wrote:
> Hello,
> How can I get the default value for a column?
> To change the default value, something like
> ALTER TABLE dummy ALTER COLUMN value SET DEFAULT -12;
> would do the job, but how could I query the value?
>
>
> Thanks in advance
>
You can select from the pg_ system tables, but there is now an easier
method with PostgreSQL 7.4+.

What you want is the (redundantly-named) "information_schema" schema in
any PostgreSQL database. I take it you have installed phpPgAdmin
(phppgadmin.sf.net)? Then just open it and navigate to
information_schema inside your database, and look for the View titled
'columns', and a column titled 'column_default'. So your query would
look something like:

SELECT column_default FROM information_schema.columns WHERE
table_name='your_table_name';

or if needed:

SELECT column_default FROM information_schema.columns WHERE
table_name='your_table_name' AND schema='whatever_schema';

(this would be needed if you have a duplicate table name in more than
one schema)

HTH,

Regards,

Rick Morris

If you want to use variables in your queries with procedural  functions
you need to build the query as a string and use EXECUTE to run it like so:

EXECUTE 'alter table accounts_post_history_payout  alter column
payout_rate set default ' || payout_rate_in || ';';

Jean-Christophe Roux wrote:
> Hi,
> Thank you very much for the tip; it works fine and I can query easily
> default values.
> I have this function:
> CREATE OR REPLACE FUNCTION
> func_accounts_post_history_default(payout_rate_in numeric)
>   RETURNS text AS
> $BODY$
> declare
>     i integer;
> begin
>     --alter table accounts_post_history_payout  alter column
> payout_rate set default payout_rate_in;
>     alter table accounts_post_history_payout  alter column payout_rate
> set default 0;
>     return 'Default values have been updated.';
> end;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
> The uncommented alter command works but the commented one does not
> work because it apparently lacks the new default value.  Any idea how
> I should change the syntax? I find it strange because I usually do not
> have any problem using parameters in my function.
>
> Thanks
>
>
>
> ----- Original Message ----
> From: Erik Jones <erik@myemma.com>
>
> >> Hello,
> >> How can I get the default value for a column?
> >> To change the default value, something like
> >> ALTER TABLE dummy ALTER COLUMN value SET DEFAULT -12;
> >> would do the job, but how could I query the value?
> >>
> >>
> >> Thanks in advance
> >
> SELECT adsrc as default_value
> FROM pg_attrdef pad, pg_atttribute pat, pg_class pc
> WHERE pc.relname='your_table_name'
>     AND pc.oid=pat.attrelid AND pat.attname='your_column_name'
>     AND pat.attrelid=pad.adrelid AND pat.attnum=pad.adnum
>
> --
> erik jones <erik@myemma.com>
> software development
> emma(r)
>
>
>


--
erik jones <erik@myemma.com>
software development
emma(r)

Yes, the manual is definitely your friend... :)

Jean-Christophe Roux wrote:
> Erik,
> Yes that's right 'execute' is the way to go. Looks like I am going to
> have to spend more time reading the docs on execute... Talking about
> reading docs, to reference your smart way to get default values, I
> have to go to 'system catalogs' at
> http://www.postgresql.org/docs/8.1/interactive/catalogs.html
> thank you
> JCR
>
> ----- Original Message ----
> From: Erik Jones <erik@myemma.com>
> To: Jean-Christophe Roux <jcxxr@yahoo.com>
> Cc: pgsql-php@postgresql.org
> Sent: Monday, November 13, 2006 2:16:24 PM
> Subject: Re: [PHP] get column default value
>
> If you want to use variables in your queries with procedural  functions
> you need to build the query as a string and use EXECUTE to run it like so:
>
> EXECUTE 'alter table accounts_post_history_payout  alter column
> payout_rate set default ' || payout_rate_in || ';';
>
> Jean-Christophe Roux wrote:
> > Hi,
> > Thank you very much for the tip; it works fine and I can query easily
> > default values.
> > I have this function:
> > CREATE OR REPLACE FUNCTION
> > func_accounts_post_history_default(payout_rate_in numeric)
> >   RETURNS text AS
> > $BODY$
> > declare
> >     i integer;
> > begin
> >     --alter table accounts_post_history_payout  alter column
> > payout_rate set default payout_rate_in;
> >     alter table accounts_post_history_payout  alter column payout_rate
> > set default 0;
> >     return 'Default values have been updated.';
> > end;
> > $BODY$
> >   LANGUAGE 'plpgsql' VOLATILE;
> >
> > The uncommented alter command works but the commented one does not
> > work because it apparently lacks the new default value.  Any idea how
> > I should change the syntax? I find it strange because I usually do not
> > have any problem using parameters in my function.
> >
> > Thanks
> >
> >
> >
> > ----- Original Message ----
> > From: Erik Jones <erik@myemma.com>
> >
> > >> Hello,
> > >> How can I get the default value for a column?
> > >> To change the default value, something like
> > >> ALTER TABLE dummy ALTER COLUMN value SET DEFAULT -12;
> > >> would do the job, but how could I query the value?
> > >>
> > >>
> > >> Thanks in advance
> > >
> > SELECT adsrc as default_value
> > FROM pg_attrdef pad, pg_atttribute pat, pg_class pc
> > WHERE pc.relname='your_table_name'
> >     AND pc.oid=pat.attrelid AND pat.attname='your_column_name'
> >     AND pat.attrelid=pad.adrelid AND pat.attnum=pad.adnum
> >
> > --
> > erik jones <erik@myemma.com>
> > software development
> > emma(r)
> >
> >
> >
>
>
> --
> erik jones <erik@myemma.com>
> software development
> emma(r)
>
>
>


--
erik jones <erik@myemma.com>
software development
emma(r)