Re: get column default value - Mailing list pgsql-php
From | Erik Jones |
---|---|
Subject | Re: get column default value |
Date | |
Msg-id | 4558CBA7.8090803@myemma.com Whole thread Raw |
In response to | Re: get column default value (Jean-Christophe Roux <jcxxr@yahoo.com>) |
List | pgsql-php |
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)