Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 84B2F9FA217 for ; Mon, 13 Nov 2006 15:01:59 -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 58344-08 for ; Mon, 13 Nov 2006 19:01:48 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web35312.mail.mud.yahoo.com (web35312.mail.mud.yahoo.com [66.163.179.106]) by postgresql.org (Postfix) with SMTP id 337BC9FA12B for ; Mon, 13 Nov 2006 15:01:48 -0400 (AST) Received: (qmail 47765 invoked by uid 60001); 13 Nov 2006 19:01:47 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:MIME-Version:Content-Type; b=2Q2rs7/sfoukXvX8Xt+hSMo/P0IoeN0JiBn6ARhJCaqjBb70oP7BwaNoXP2zT/47z+44oR96L3fJOKkOH7OQKKOe/ccXLg/PGbSlaprL8/OagU2/Mmt3cNpjdmmX4As7OBQiJyCE79yiIsrZeL7p3VZnqdS/LJnx0DJWohtQr9A= ; Message-ID: <20061113190147.47763.qmail@web35312.mail.mud.yahoo.com> Received: from [74.98.189.48] by web35312.mail.mud.yahoo.com via HTTP; Mon, 13 Nov 2006 11:01:47 PST Date: Mon, 13 Nov 2006 11:01:47 -0800 (PST) From: Jean-Christophe Roux Subject: Re: get column default value To: Erik Jones Cc: pgsql-php@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1729959099-1163444507=:46553" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/47 X-Sequence-Number: 3664 --0-1729959099-1163444507=:46553 Content-Type: text/plain; charset=ascii Content-Transfer-Encoding: quoted-printable Hi,=0AThank you very much for the tip; it works fine and I can query easily= default values.=0AI have this function:=0ACREATE OR REPLACE FUNCTION func_= accounts_post_history_default(payout_rate_in numeric)=0A RETURNS text AS= =0A$BODY$=0Adeclare=0A i integer;=0Abegin=0A --alter table accounts_p= ost_history_payout alter column payout_rate set default payout_rate_in;=0A= alter table accounts_post_history_payout alter column payout_rate set = default 0;=0A return 'Default values have been updated.';=0Aend;=0A$BODY= $=0A LANGUAGE 'plpgsql' VOLATILE;=0A=0AThe uncommented alter command works= but the commented one does not work because it apparently lacks the new de= fault value. Any idea how I should change the syntax? I find it strange be= cause I usually do not have any problem using parameters in my function.=0A= =0AThanks=0A=0A=0A=0A----- Original Message ----=0AFrom: Erik Jones =0A=0A>> Hello,=0A>> How can I get the default value for a column= ?=0A>> To change the default value, something like=0A>> ALTER TABLE dummy A= LTER COLUMN value SET DEFAULT -12;=0A>> would do the job, but how could I q= uery the value?=0A>>=0A>>=0A>> Thanks in advance=0A>=0ASELECT adsrc as defa= ult_value=0AFROM pg_attrdef pad, pg_atttribute pat, pg_class pc=0AWHERE pc.= relname=3D'your_table_name'=0A AND pc.oid=3Dpat.attrelid AND pat.attname= =3D'your_column_name'=0A AND pat.attrelid=3Dpad.adrelid AND pat.attnum= =3Dpad.adnum=0A=0A-- =0Aerik jones =0Asoftware development= =0Aemma(r)=0A=0A=0A=0A=0A=0A=0A=0A --0-1729959099-1163444507=:46553 Content-Type: text/html; charset=ascii Content-Transfer-Encoding: quoted-printable
Hi,
Thank you very much for the tip; it works fi= ne and I can query easily default values.
I have this function:
CREAT= E OR REPLACE FUNCTION func_accounts_post_history_default(payout_rate_in num= eric)
  RETURNS text AS
$BODY$
declare
    = i integer;
begin
    --alter table accounts_post_histo= ry_payout  alter column payout_rate set default payout_rate_in;
&nb= sp;   alter table accounts_post_history_payout  alter column= payout_rate set default 0;
    return 'Default values ha= ve 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 us= ing parameters in my function.

Thanks



----- O= riginal 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 jo= b, but how could I query the value?
>>
>>
>> Tha= nks in advance
>
SELECT adsrc as default_value
FROM pg_attrdef = pad, pg_atttribute pat, pg_class pc
WHERE pc.relname=3D'your_table_name'=
    AND pc.oid=3Dpat.attrelid AND pat.attname=3D'yo= ur_column_name'
    AND pat.attrelid=3Dpad.adrelid A= ND pat.attnum=3Dpad.adnum

--
erik jones <erik@myemma.com>software development
emma(r)



--0-1729959099-1163444507=:46553--