Re: get column default value

[Erik Jones <erik(at)myemma(dot)com>]

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(at)myemma(dot)com>
> To: Jean-Christophe Roux <jcxxr(at)yahoo(dot)com>
> Cc: pgsql-php(at)postgresql(dot)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(at)myemma(dot)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(at)myemma(dot)com>
> > software development
> > emma(r)
> >
> >
> >
>
>
> --
> erik jones <erik(at)myemma(dot)com>
> software development
> emma(r)


--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)