From jcxxr@yahoo.com Mon Nov 13 14:49:19 2006 Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id A4C549FA1DB for ; Mon, 13 Nov 2006 10:49:19 -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 72446-06 for ; Mon, 13 Nov 2006 10:49:17 -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 35D959FA18A for ; Mon, 13 Nov 2006 10:49:15 -0400 (AST) Received: (qmail 83396 invoked by uid 60001); 13 Nov 2006 14:49:14 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type; b=XhtLyMAGNXGAKU6ZCgWdU+oO+ME3CYlaEfUJ2p1Hrj7NY5NddiI81HH+VbGFFQuL1hRtva8rdwJG5rivXIT3OjDGD3HTB8iMYBPWus0KOMO6PH/B1qhFyo0SmxunsPWQM8v4q3lcuYYu0KtG9ZcD9nJsbuS3T71YBbh/LMXsZQQ= ; Message-ID: <20061113144914.83394.qmail@web35307.mail.mud.yahoo.com> Received: from [74.98.189.48] by web35307.mail.mud.yahoo.com via HTTP; Mon, 13 Nov 2006 06:49:14 PST Date: Mon, 13 Nov 2006 06:49:14 -0800 (PST) From: Jean-Christophe Roux Subject: get column default value To: pgsql-php@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-759848485-1163429354=:82336" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.616 tagged_above=0 required=5 tests=AWL, BAYES_20, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_40_50, HTML_MESSAGE X-Spam-Level: * X-Archive-Number: 200611/41 X-Sequence-Number: 3658 --0-759848485-1163429354=:82336 Content-Type: text/plain; charset=ascii Content-Transfer-Encoding: quoted-printable Hello,=0AHow can I get the default value for a column?=0ATo change the defa= ult value, something like=0AALTER TABLE dummy ALTER COLUMN value SET DEFAUL= T -12;=0Awould do the job, but how could I query the value?=0A=0A=0AThanks = in advance=0A=0A=0A=0A --0-759848485-1163429354=:82336 Content-Type: text/html; charset=ascii Content-Transfer-Encoding: quoted-printable
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 t= he value?


Thanks in advance

--0-759848485-1163429354=:82336-- From ketema@gmail.com Mon Nov 13 14:59:53 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 0DB319FA18A for ; Mon, 13 Nov 2006 10:59:53 -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 90227-09 for ; Mon, 13 Nov 2006 14:59:43 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.231]) by postgresql.org (Postfix) with ESMTP id EDDF09FA1DB for ; Mon, 13 Nov 2006 10:59:42 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so1191777wxd for ; Mon, 13 Nov 2006 06:59:41 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:in-reply-to:references:mime-version:content-type:message-id:cc:from:subject:date:to:x-mailer; b=s3Q2eUklH9S65eZv63faepr/uEA/Yuue+QFt+/VunFk4UGuOyA5PP2Okz61xRl6o1wsDT+MoNLFtuSs7vdg+oiB23GBcsj6JyCLfOYB8mlQE8jkP7BtgHkL6odBK3K6rf0udSdper9o8BcOI0r/hIJnYIfbreWERCBPjEyqoHUY= Received: by 10.70.43.8 with SMTP id q8mr10010092wxq.1163429980503; Mon, 13 Nov 2006 06:59:40 -0800 (PST) Received: from ?192.168.0.12? ( [24.227.61.54]) by mx.google.com with ESMTP id h38sm10699512wxd.2006.11.13.06.59.39; Mon, 13 Nov 2006 06:59:39 -0800 (PST) In-Reply-To: <20061113144914.83394.qmail@web35307.mail.mud.yahoo.com> References: <20061113144914.83394.qmail@web35307.mail.mud.yahoo.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: multipart/alternative; boundary=Apple-Mail-70-439175372 Message-Id: <73E54DDE-B024-49FD-8247-739F9B73532A@gmail.com> Cc: pgsql-php@postgresql.org From: Ketema Harris Subject: Re: get column default value Date: Mon, 13 Nov 2006 09:59:29 -0500 To: Jean-Christophe Roux X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/42 X-Sequence-Number: 3659 --Apple-Mail-70-439175372 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed The default value for a column is just that the value you say is default. It only come into play when you do an insert into the table. If you do not provide a value for that column then your default value is entered. Now if you set the default value of a column to a sequence, then you can manage the sequence using the sequence functions: currval, nextval, and setval. Sequences and their functions are documented in the pg manual. On Nov 13, 2006, at 9:49 AM, Jean-Christophe Roux wrote: > 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 > --Apple-Mail-70-439175372 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1 The default value for a column = is just that the value you say is default.=A0 It only come into play = when you do an insert into the table.=A0 If you do not provide a value = for that column then your default value is entered.

Now if you set the default = value of a column to a sequence, then you can manage the sequence using = the sequence functions: currval, nextval, and setval.=A0 Sequences and = their functions are documented in the pg = manual.

On Nov 13, 2006, at 9:49 AM, = Jean-Christophe Roux wrote:

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
=0ATo: Jean-Christophe Roux =0ACc: pgsql-php@postgre= sql.org=0ASent: Monday, November 13, 2006 9:59:29 AM=0ASubject: Re: [PHP] g= et column default value=0A=0AThe default value for a column is just that th= e value you say is default. It only come into play when you do an insert i= nto the table. If you do not provide a value for that column then your def= ault value is entered.=0A=0ANow if you set the default value of a column to= a sequence, then you can manage the sequence using the sequence functions:= currval, nextval, and setval. Sequences and their functions are documente= d in the pg manual.=0A=0AOn Nov 13, 2006, at 9:49 AM, Jean-Christophe Roux = wrote:=0A=0AHello,=0AHow can I get the default value for a column?=0ATo cha= nge the default value, something like=0AALTER TABLE dummy ALTER COLUMN valu= e SET DEFAULT -12;=0Awould do the job, but how could I query the value?=0A= =0A=0AThanks in advance=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A --0-1962304549-1163430232=:91732 Content-Type: text/html; charset=ascii Content-Transfer-Encoding: quoted-printable
I understand that but I need to build an interface = so that my user can change the default values of some table/columns. So I n= eed a way to query the current default value to show the user what it is cu= rrently. For instance, let's say I have a table products and there is a col= umn price and the default price is $199, I'd like to get that $199 and give= my user the opportunity to update that value. The column does not have any= sequence attached to it.
JCR

----- Original Message ----=
From: Ketema Harris <ketema@gmail.com>
To: Jean-Christophe Rou= x <jcxxr@yahoo.com>
Cc: pgsql-php@postgresql.org
Sent: Monday, = November 13, 2006 9:59:29 AM
Subject: Re: [PHP] get column default value

The defau= lt value for a column is just that the value you say is default.  It o= nly come into play when you do an insert into the table.  If you do no= t provide a value for that column then your default value is entered.
<= br class=3D"khtml-block-placeholder">
Now if you set the default = value of a column to a sequence, then you can manage the sequence using the= sequence functions: currval, nextval, and setval.  Sequences and thei= r functions are documented in the pg manual.

On Nov= 13, 2006, at 9:49 AM, Jean-Christophe Roux wrote:

<= div style=3D"font-family: times new roman; font-size: 16px;">Hello,
= How can I get the default value for a column?
To chan= ge 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

=



--0-1962304549-1163430232=:91732-- From erik@myemma.com Mon Nov 13 15:19:36 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 597DD9FB20F for ; Mon, 13 Nov 2006 11:19:36 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 36270-03 for ; Mon, 13 Nov 2006 11:19:30 -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 9B4E09FB1F2 for ; Mon, 13 Nov 2006 11:19:28 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by myemma.com (Postfix) with ESMTP id 0567723CE3; Mon, 13 Nov 2006 09:19:27 -0600 (CST) Received: from myemma.com ([127.0.0.1]) by localhost (velma [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22568-08; Mon, 13 Nov 2006 09:19: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 4544023CDB; Mon, 13 Nov 2006 09:19:26 -0600 (CST) Message-ID: <45588CFC.4000304@myemma.com> Date: Mon, 13 Nov 2006 09:19:24 -0600 From: Erik Jones User-Agent: Thunderbird 1.5.0.7 (Windows/20060909) MIME-Version: 1.0 To: Jean-Christophe Roux Cc: Ketema Harris , pgsql-php@postgresql.org Subject: Re: get column default value References: <20061113150352.91895.qmail@web35303.mail.mud.yahoo.com> In-Reply-To: <20061113150352.91895.qmail@web35303.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/44 X-Sequence-Number: 3661 Jean-Christophe Roux wrote: > I understand that but I need to build an interface so that my user can > change the default values of some table/columns. So I need a way to > query the current default value to show the user what it is currently. > For instance, let's say I have a table products and there is a column > price and the default price is $199, I'd like to get that $199 and > give my user the opportunity to update that value. The column does not > have any sequence attached to it. > JCR > > ----- Original Message ---- > From: Ketema Harris > To: Jean-Christophe Roux > Cc: pgsql-php@postgresql.org > Sent: Monday, November 13, 2006 9:59:29 AM > Subject: Re: [PHP] get column default value > > The default value for a column is just that the value you say is > default. It only come into play when you do an insert into the > table. If you do not provide a value for that column then your > default value is entered. > > Now if you set the default value of a column to a sequence, then you > can manage the sequence using the sequence functions: currval, > nextval, and setval. Sequences and their functions are documented in > the pg manual. > > On Nov 13, 2006, at 9:49 AM, Jean-Christophe Roux wrote: > >> 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) From rick@brainscraps.com Mon Nov 13 15:23:01 2006 Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id B0A2D9FB20F for ; Mon, 13 Nov 2006 11:23:01 -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 83964-03 for ; Mon, 13 Nov 2006 11:22:57 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from imf25aec.mail.bellsouth.net (imf25aec.mail.bellsouth.net [205.152.59.73]) by postgresql.org (Postfix) with ESMTP id 034019FB1F2 for ; Mon, 13 Nov 2006 11:22:56 -0400 (AST) Received: from ibm69aec.bellsouth.net ([65.3.167.212]) by imf25aec.mail.bellsouth.net with ESMTP id <20061113152255.FTS2981.imf25aec.mail.bellsouth.net@ibm69aec.bellsouth.net> for ; Mon, 13 Nov 2006 10:22:55 -0500 Received: from [192.168.1.5] (really [65.3.167.212]) by ibm69aec.bellsouth.net with ESMTP id <20061113152250.VJBM21497.ibm69aec.bellsouth.net@[192.168.1.5]>; Mon, 13 Nov 2006 10:22:50 -0500 Message-ID: <4558AA88.3070102@brainscraps.com> Date: Mon, 13 Nov 2006 12:25:28 -0500 From: Rick Morris User-Agent: Thunderbird 1.5.0.5 (X11/20060904) MIME-Version: 1.0 To: Jean-Christophe Roux CC: pgsql-php@postgresql.org Subject: Re: get column default value References: <20061113144914.83394.qmail@web35307.mail.mud.yahoo.com> In-Reply-To: <20061113144914.83394.qmail@web35307.mail.mud.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/45 X-Sequence-Number: 3662 Jean-Christophe Roux wrote: > 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 > You can select from the pg_ system tables, but there is now an easier method with PostgreSQL 7.4+. What you want is the (redundantly-named) "information_schema" schema in any PostgreSQL database. I take it you have installed phpPgAdmin (phppgadmin.sf.net)? Then just open it and navigate to information_schema inside your database, and look for the View titled 'columns', and a column titled 'column_default'. So your query would look something like: SELECT column_default FROM information_schema.columns WHERE table_name='your_table_name'; or if needed: SELECT column_default FROM information_schema.columns WHERE table_name='your_table_name' AND schema='whatever_schema'; (this would be needed if you have a duplicate table name in more than one schema) HTH, Regards, Rick Morris From jcxxr@yahoo.com Mon Nov 13 19:01:59 2006 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-- From erik@myemma.com Mon Nov 13 19:16:29 2006 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) From jcxxr@yahoo.com Mon Nov 13 19:43:04 2006 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-- From erik@myemma.com Mon Nov 13 19:47:00 2006 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)