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)