Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id CBE7F9FB269 for ; Mon, 13 Nov 2006 15:47:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 67409-10 for ; Mon, 13 Nov 2006 19:46:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from myemma.com (db1.e2ma.net [66.179.147.163]) by postgresql.org (Postfix) with ESMTP id 15FDC9FA6B5 for ; Mon, 13 Nov 2006 15:46:51 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by myemma.com (Postfix) with ESMTP id 0DA5823CE3; Mon, 13 Nov 2006 13:46:50 -0600 (CST) Received: from myemma.com ([127.0.0.1]) by localhost (velma [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21369-01; Mon, 13 Nov 2006 13:46:49 -0600 (CST) Received: from [192.168.1.11] (c-71-228-218-92.hsd1.tn.comcast.net [71.228.218.92]) by myemma.com (Postfix) with ESMTP id 5027823CDB; Mon, 13 Nov 2006 13:46:49 -0600 (CST) Message-ID: <4558CBA7.8090803@myemma.com> Date: Mon, 13 Nov 2006 13:46:47 -0600 From: Erik Jones User-Agent: Thunderbird 1.5.0.7 (Windows/20060909) MIME-Version: 1.0 To: Jean-Christophe Roux Cc: pgsql-php@postgresql.org Subject: Re: get column default value References: <20061113194258.96874.qmail@web35307.mail.mud.yahoo.com> In-Reply-To: <20061113194258.96874.qmail@web35307.mail.mud.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at myemma.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/50 X-Sequence-Number: 3667 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 > To: Jean-Christophe Roux > 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 > > > > >> 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 > > software development > > emma(r) > > > > > > > > > -- > erik jones > software development > emma(r) > > > -- erik jones software development emma(r)