pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedget column default value
9+ messages / 4 participants
[nested] [flat]
* get column default value
@ 2006-11-13 14:49 Jean-Christophe Roux <jcxxr@yahoo.com>
2006-11-13 14:59 ` Re: get column default value Ketema Harris <ketema@gmail.com>
2006-11-13 17:25 ` Re: get column default value Rick Morris <rick@brainscraps.com>
0 siblings, 2 replies; 9+ messages in thread
From: Jean-Christophe Roux @ 2006-11-13 14:49 UTC (permalink / raw)
To: pgsql-php
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: get column default value
2006-11-13 14:49 get column default value Jean-Christophe Roux <jcxxr@yahoo.com>
@ 2006-11-13 14:59 ` Ketema Harris <ketema@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Ketema Harris @ 2006-11-13 14:59 UTC (permalink / raw)
To: Jean-Christophe Roux <jcxxr@yahoo.com>; +Cc: pgsql-php
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: get column default value
2006-11-13 14:49 get column default value Jean-Christophe Roux <jcxxr@yahoo.com>
@ 2006-11-13 17:25 ` Rick Morris <rick@brainscraps.com>
1 sibling, 0 replies; 9+ messages in thread
From: Rick Morris @ 2006-11-13 17:25 UTC (permalink / raw)
To: Jean-Christophe Roux <jcxxr@yahoo.com>; +Cc: pgsql-php
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: get column default value
@ 2006-11-13 15:03 Jean-Christophe Roux <jcxxr@yahoo.com>
2006-11-13 15:19 ` Re: get column default value Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jean-Christophe Roux @ 2006-11-13 15:03 UTC (permalink / raw)
To: Ketema Harris <ketema@gmail.com>; +Cc: pgsql-php
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 <ketema@gmail.com>
To: Jean-Christophe Roux <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 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: get column default value
2006-11-13 15:03 Re: get column default value Jean-Christophe Roux <jcxxr@yahoo.com>
@ 2006-11-13 15:19 ` Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 9+ messages in thread
From: Erik Jones @ 2006-11-13 15:19 UTC (permalink / raw)
To: Jean-Christophe Roux <jcxxr@yahoo.com>; +Cc: Ketema Harris <ketema@gmail.com>; pgsql-php
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 <ketema@gmail.com>
> To: Jean-Christophe Roux <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 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 <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: get column default value
@ 2006-11-13 19:01 Jean-Christophe Roux <jcxxr@yahoo.com>
2006-11-13 19:16 ` Re: get column default value Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jean-Christophe Roux @ 2006-11-13 19:01 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-php
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 <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
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 <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: get column default value
2006-11-13 19:01 Re: get column default value Jean-Christophe Roux <jcxxr@yahoo.com>
@ 2006-11-13 19:16 ` Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 9+ messages in thread
From: Erik Jones @ 2006-11-13 19:16 UTC (permalink / raw)
To: Jean-Christophe Roux <jcxxr@yahoo.com>; +Cc: pgsql-php
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 <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
> 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 <erik@myemma.com>
> software development
> emma(r)
>
>
>
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: get column default value
@ 2006-11-13 19:42 Jean-Christophe Roux <jcxxr@yahoo.com>
2006-11-13 19:46 ` Re: get column default value Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jean-Christophe Roux @ 2006-11-13 19:42 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-php
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 <erik@myemma.com>
To: Jean-Christophe Roux <jcxxr@yahoo.com>
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 <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
> 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 <erik@myemma.com>
> software development
> emma(r)
>
>
>
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: get column default value
2006-11-13 19:42 Re: get column default value Jean-Christophe Roux <jcxxr@yahoo.com>
@ 2006-11-13 19:46 ` Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 9+ messages in thread
From: Erik Jones @ 2006-11-13 19:46 UTC (permalink / raw)
To: Jean-Christophe Roux <jcxxr@yahoo.com>; +Cc: pgsql-php
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 <erik@myemma.com>
> To: Jean-Christophe Roux <jcxxr@yahoo.com>
> 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 <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
> > 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 <erik@myemma.com>
> > software development
> > emma(r)
> >
> >
> >
>
>
> --
> erik jones <erik@myemma.com>
> software development
> emma(r)
>
>
>
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2006-11-13 19:46 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-13 14:49 get column default value Jean-Christophe Roux <jcxxr@yahoo.com>
2006-11-13 14:59 ` Ketema Harris <ketema@gmail.com>
2006-11-13 17:25 ` Rick Morris <rick@brainscraps.com>
2006-11-13 15:03 Re: get column default value Jean-Christophe Roux <jcxxr@yahoo.com>
2006-11-13 15:19 ` Erik Jones <erik@myemma.com>
2006-11-13 19:01 Re: get column default value Jean-Christophe Roux <jcxxr@yahoo.com>
2006-11-13 19:16 ` Erik Jones <erik@myemma.com>
2006-11-13 19:42 Re: get column default value Jean-Christophe Roux <jcxxr@yahoo.com>
2006-11-13 19:46 ` Erik Jones <erik@myemma.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox