get column default value

--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? =0AThanks =
in advance
--0-759848485-1163429354=:82336
Content-Type: text/html; charset=ascii
Content-Transfer-Encoding: quoted-printable

ad>

Re: get column default value

--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

-khtml-line-break: after-white-space; ">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.

Re: get column default value

--0-1962304549-1163430232=:91732
Content-Type: text/plain; charset=ascii
Content-Transfer-Encoding: quoted-printable

I understand that but I need to build an interface so that my user can chan=
ge the default values of some table/columns. So I need a way to query the c=
urrent default value to show the user what it is currently. For instance, l=
et's say I have a table products and there is a column price and the defaul=
t 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=
..=0AJCR ----- Original Message ----=0AFrom: Ketema Harris l.com>=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 The 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. 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 documente=
d in the pg manual. On Nov 13, 2006, at 9:49 AM, Jean-Christophe Roux =
wrote: Hello,=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=
Thanks in advance
--0-1962304549-1163430232=:91732
Content-Type: text/html; charset=ascii
Content-Transfer-Encoding: quoted-printable

ad>

Re: get column default value

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)


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: get column default value

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: get column default value

--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; The 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 ----- Original Message ----=0AFrom: Erik Jones yemma.com> >> 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 -- =0Aerik jones =0Asoftware development=
=0Aemma(r)
--0-1729959099-1163444507=:46553
Content-Type: text/html; charset=ascii
Content-Transfer-Encoding: quoted-printable

ad>

Re: 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)


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: get column default value

--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 ----- 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 If 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: EXECUTE 'alter table ac=
counts_post_history_payout alter column =0Apayout_rate set default ' || pa=
yout_rate_in || ';'; Jean-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-- =0Aerik jones emma.com>=0Asoftware development=0Aemma(r)
--0-423758853-1163446978=:96861
Content-Type: text/html; charset=ascii
Content-Transfer-Encoding: quoted-printable

ad>

Re: get column default value

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)


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate