pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
get 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