Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id 6A8049FB26A for ; Mon, 13 Nov 2006 15:43:04 -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 64463-08 for ; Mon, 13 Nov 2006 15:43:01 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web35307.mail.mud.yahoo.com (web35307.mail.mud.yahoo.com [66.163.179.101]) by postgresql.org (Postfix) with SMTP id 4DF369FB269 for ; Mon, 13 Nov 2006 15:42:59 -0400 (AST) Received: (qmail 96876 invoked by uid 60001); 13 Nov 2006 19:42:58 -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=KggivXFzxYxMWuJ+d15lJFc42ojTLyKrdDigXXQ6sxOJLOepAkm2OQaKIGLP6NLLnrzRwBOTTgd4cUyxyOlD/AgtUijwLbzCrGfAcWxmxVjLZgJ+CwtUjgc6GX4qQjxBfQUj6VrUqborN8y7WdP+0BMrZEyaWkSJhUvjd2RW7Y8= ; Message-ID: <20061113194258.96874.qmail@web35307.mail.mud.yahoo.com> Received: from [74.98.189.48] by web35307.mail.mud.yahoo.com via HTTP; Mon, 13 Nov 2006 11:42:58 PST Date: Mon, 13 Nov 2006 11:42:58 -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-423758853-1163446978=:96861" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.371 tagged_above=0 required=5 tests=AWL, BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_30_40, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200611/49 X-Sequence-Number: 3666 --0-423758853-1163446978=:96861 Content-Type: text/plain; charset=ascii Content-Transfer-Encoding: quoted-printable Erik,=0AYes 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 rea= ding docs, to reference your smart way to get default values, I have to go = to 'system catalogs' at=0Ahttp://www.postgresql.org/docs/8.1/interactive/ca= talogs.html=0Athank you=0AJCR=0A=0A----- Original Message ----=0AFrom: Erik= Jones =0ATo: Jean-Christophe Roux =0ACc:= pgsql-php@postgresql.org=0ASent: Monday, November 13, 2006 2:16:24 PM=0ASu= bject: Re: [PHP] get column default value=0A=0AIf you want to use variables= in your queries with procedural functions =0Ayou need to build the query = as a string and use EXECUTE to run it like so:=0A=0AEXECUTE 'alter table ac= counts_post_history_payout alter column =0Apayout_rate set default ' || pa= yout_rate_in || ';';=0A=0AJean-Christophe Roux wrote:=0A> Hi,=0A> Thank you= very much for the tip; it works fine and I can query easily =0A> default v= alues.=0A> I have this function:=0A> CREATE OR REPLACE FUNCTION =0A> func_a= ccounts_post_history_default(payout_rate_in numeric)=0A> RETURNS text AS= =0A> $BODY$=0A> declare=0A> i integer;=0A> begin=0A> --alter table = accounts_post_history_payout alter column =0A> payout_rate set default pay= out_rate_in;=0A> alter table accounts_post_history_payout alter column= payout_rate =0A> set default 0;=0A> return 'Default values have been u= pdated.';=0A> end;=0A> $BODY$=0A> LANGUAGE 'plpgsql' VOLATILE;=0A>=0A> Th= e uncommented alter command works but the commented one does not =0A> work = because it apparently lacks the new default value. Any idea how =0A> I sho= uld change the syntax? I find it strange because I usually do not =0A> have= any problem using parameters in my function.=0A>=0A> Thanks=0A>=0A>=0A>=0A= > ----- Original Message ----=0A> From: 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 ALTER COL= UMN value SET DEFAULT -12;=0A> >> would do the job, but how could I query t= he value?=0A> >>=0A> >>=0A> >> Thanks in advance=0A> >=0A> SELECT adsrc as = default_value=0A> FROM pg_attrdef pad, pg_atttribute pat, pg_class pc=0A> W= HERE pc.relname=3D'your_table_name'=0A> AND pc.oid=3Dpat.attrelid AND p= at.attname=3D'your_column_name'=0A> AND pat.attrelid=3Dpad.adrelid AND = pat.attnum=3Dpad.adnum=0A>=0A> -- =0A> erik jones =0A> sof= tware development=0A> emma(r)=0A>=0A>=0A>=0A=0A=0A-- =0Aerik jones =0Asoftware development=0Aemma(r)=0A=0A=0A=0A=0A=0A=0A=0A --0-423758853-1163446978=:96861 Content-Type: text/html; charset=ascii Content-Transfer-Encoding: quoted-printable
Erik,
Yes that's right 'execute' is the way to g= o. Looks like I am going to have to spend more time reading the docs on exe= cute... Talking about reading docs, to reference your smart way to get defa= ult values, I have to go to 'system catalogs' at
h= ttp://www.postgresql.org/docs/8.1/interactive/catalogs.html
t= hank you
JCR

----- Original Message ----
From: Erik Jo= nes <erik@myemma.com>
To: Jean-Christophe Roux <jcxxr@yahoo.com= >
Cc: pgsql-php@postgresql.org
Sent: Monday, November 13, 2006 2:1= 6: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 th= e query as a string and use EXECUTE to run it like so:

EXECUTE 'alte= r table accounts_post_history_payout  alter column
payout_rat= e 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&n= bsp; 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 uncommen= ted alter command works but the commented one does not
> work becaus= e it apparently lacks the new default value.  Any idea how
&g= t; I should change the syntax? I find it strange because I usually do not <= br>> have any problem using parameters in my function.
>
> T= hanks
>
>
>
> ----- Original Message ----
> F= rom: 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
&g= t; FROM pg_attrdef pad, pg_atttribute pat, pg_class pc
> WHERE pc.rel= name=3D'your_table_name'
>     AND pc.oid=3Dpat.a= ttrelid AND pat.attname=3D'your_column_name'
>    = ; AND pat.attrelid=3Dpad.adrelid AND pat.attnum=3Dpad.adnum
>
>= --
> erik jones <erik@myemma.com>
> software developmen= t
> emma(r)
>
>
>


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

<= br>

--0-423758853-1163446978=:96861--