Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id 480229FB26A for ; Mon, 13 Nov 2006 15:16:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 56886-09 for ; Mon, 13 Nov 2006 15:16:27 -0400 (AST) 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 646D79FA3C0 for ; Mon, 13 Nov 2006 15:16:27 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by myemma.com (Postfix) with ESMTP id 92D0723CDB; Mon, 13 Nov 2006 13:16:26 -0600 (CST) Received: from myemma.com ([127.0.0.1]) by localhost (velma [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15423-06; Mon, 13 Nov 2006 13:16:26 -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 E1FC223CD4; Mon, 13 Nov 2006 13:16:25 -0600 (CST) Message-ID: <4558C488.8090804@myemma.com> Date: Mon, 13 Nov 2006 13:16:24 -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: <20061113190147.47763.qmail@web35312.mail.mud.yahoo.com> In-Reply-To: <20061113190147.47763.qmail@web35312.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/48 X-Sequence-Number: 3665 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)