[SQL] Query to return schema/table/columname/columntype
Hi all, I am trying to modify the dabo (a python wxpython ide for database forms creation) code to allow the selection of tables in any schema. I need a query that will return records with schema, table, columname and columne type. For background I am selecting table & schema by the query: SELECT schemaname || '.' || tablename AS tablename FROM pg_tables ORDER BY tablename; Then I need to obtain the columns within that table/schema. The original query was: select c.oid,a.attname, t.typname from pg_class c inner join pg_attribute a on a.attrelid = c.oid inner join pg_type t on a.atttypid = t.oid where c.relname = 'thetablename and a.attnum > 0; Now my problem is how to create a new column to replace c.relname so I can query on "theschemaname.thetablename". Any suggestions will be welcomed. Thanks, Phil ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Postgresql & Oracle Heteregenous services - strange behaviour
Dnia Wed, 17 Jan 2007 13:04:28 +, Richard Huxton napisał(a): > That'd be my guess. And then it's not fetching any rows, expecting > cursor-like behaviour. Of course we fetch all the rows before returning > any results. > > The real solution would be to add "LIMIT 0" or "LIMIT 1" to the > column-finding query, but I doubt that's possible with the Oracle plugin. > > Perhaps check if there's a "fetch N rows at a time" option for the ODBC > setup that might help you. I've tried various settings, upgraded to psqlodbc 8.02.0200, looked into the source (info.c, retry_public_schema label) and there's a query which gets the columns (select n.nspname, c.relname, a.attname, a.atttypid (...)), I now can even see it in logs but there's still this additional select before. I think it's not psqlodbc problem, because when I issue my queries directly from isql from unixodbc everything seems normal - I get this additional select only when quering from Oracle using @my_server. Therefore, I think it is Oracle's fault and try to move this topic to Metalink. Thank you again. -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Query to return schema/table/columname/columntype
Hi all, I think I have fixed my own problem. At: http://developer.postgresql.org/~momjian/upgrade_tips_7.3 I found the answer which was: SELECT a.attrelid as oid, a.attname, t.typname FROM pg_attribute a inner join pg_type t on a.atttypid = t.oid WHERE a.attrelid = 'co.hole_test'::regclass AND a.attnum > 0 ORDER BY a.attnum; thanks anyway! Phil Hi all, I am trying to modify the dabo (a python wxpython ide for database forms creation) code to allow the selection of tables in any schema. I need a query that will return records with schema, table, columname and columne type. For background I am selecting table & schema by the query: SELECT schemaname || '.' || tablename AS tablename FROM pg_tables ORDER BY tablename; Then I need to obtain the columns within that table/schema. The original query was: select c.oid,a.attname, t.typname from pg_class c inner join pg_attribute a on a.attrelid = c.oid inner join pg_type t on a.atttypid = t.oid where c.relname = 'thetablename and a.attnum > 0; Now my problem is how to create a new column to replace c.relname so I can query on "theschemaname.thetablename". Any suggestions will be welcomed. Thanks, Phil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Query to return schema/table/columname/columntype
On Fri, Jan 19, 2007 at 12:41:19 +, [EMAIL PROTECTED] wrote: > For background I am selecting table & schema by > the query: > SELECT schemaname || '.' || tablename AS tablename > FROM pg_tables ORDER BY tablename; Are you guaranteed that all of the names are lower case? If not you may want to include quoting in your query. I didn't have a good answer for the base question you asked; this was more under the suggestions category. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Query to return schema/table/columname/columntype
> I am trying to modify the dabo (a python wxpython > ide for database forms creation) code to allow the > selection of tables in any schema. I need a query > that will return records with schema, table, > columname and columne type. create view pg_cols as select s.nspname as schema_nm, r.relname as table_nm, a.attnum as column_sq, a.attname as column_nm, t.typname as datatype, a.atttypmod as datalen frompg_attribute a joinpg_type t on( t.oid = a.atttypid ) joinpg_class r on( r.oid = a.attrelid ) joinpg_namespace s on( s.oid = r.relnamespace ) where a.attnum > 0 and r.relkind = 'r' and s.nspname = :schemaq; Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Query to return schema/table/columname/columntype
You mean like this: CREATE OR REPLACE VIEW sys_tabledef AS SELECT columns.table_catalog, columns.table_schema, columns.table_name, columns.column_name, columns.ordinal_position, columns.column_default, columns.is_nullable, columns.data_type, columns.character_maximum_length, columns.character_octet_length, columns.numeric_precision, columns.numeric_precision_radix, columns.numeric_scale, columns.datetime_precision, columns.interval_type, columns.interval_precision, columns.character_set_catalog, columns.character_set_schema, columns.character_set_name, columns.collation_catalog, columns.collation_schema, columns.collation_name, columns.domain_catalog, columns.domain_schema, columns.domain_name, columns.udt_catalog, columns.udt_schema, columns.udt_name, columns.scope_catalog, columns.scope_schema, columns.scope_name, columns.maximum_cardinality, columns.dtd_identifier, columns.is_self_referencing FROM information_schema.columns WHERE columns.table_schema::text = 'public'::text ORDER BY columns.table_name, columns.ordinal_position; <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi all, > > I am trying to modify the dabo (a python wxpython > ide for database forms creation) code to allow the > selection of tables in any schema. I need a query > that will return records with schema, table, > columname and columne type. > > For background I am selecting table & schema by > the query: > SELECT schemaname || '.' || tablename AS tablename > FROM pg_tables ORDER BY tablename; > > Then I need to obtain the columns within that > table/schema. The original query was: > select c.oid,a.attname, t.typname > from pg_class c inner join pg_attribute a on > a.attrelid = c.oid inner join pg_type t on > a.atttypid = t.oid where c.relname = 'thetablename > and a.attnum > 0; > > Now my problem is how to create a new column to > replace c.relname so I can query on > "theschemaname.thetablename". > > Any suggestions will be welcomed. Thanks, > > Phil > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Permissions Query?
Hi all, still working on dabo a bit. Thanks for all the earlier suggestions, they are working well. I now want to know if the user/role has permision to the schema.table. How do I query to pgsql to know what permissions a particular user has on the table? I was thinking something along the lines of a query that maybe produces results like this. schematablep_select p_delete p_all p_update .. public.tbl1 10 0 0 public.tbl2 11 1 1 public.tbl00 0 1 I am intersted to see if I can filter what tables/schemas are avaliable to the user to avoid error messages due to permissions. thanks, Phil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Permissions Query?
On Fri, Jan 19, 2007 at 07:11:00PM +, [EMAIL PROTECTED] wrote: > I now want to know if the user/role has permision > to the schema.table. > > How do I query to pgsql to know what permissions a > particular user has on the table? Start here: http://www.postgresql.org/docs/8.1/interactive/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] select based on multi-column primary keys
I have set up a table with a multi-column primary key constraint:
CREATE TABLE iopoints
(
enclosureid numeric(3) NOT NULL,
pointid char(4) NOT NULL,
equipmentgroup varchar(64) NOT NULL,
deviceid varchar(8),
devicetype varchar(24),
operationdesc varchar(64) NOT NULL,
entrytime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with
time zone,
systemid numeric(3) NOT NULL,
CONSTRAINT "ID" PRIMARY KEY (systemid, enclosureid, pointid)
)
WITHOUT OIDS;
If I had a row in the table where systemid=123, enclosureid=ab,
pointid=56, I would have a Primary Key ("ID") of 123ab56 for that row.
I now want to run a select based on the Primary Key, something like:
SELECT * FROM iopoints WHERE ID = 123ab56
Is something like this even possible? Or am I forced to do:
SELECT * FROM iopoints WHERE systemid=123 AND enclosureid=ab AND pointid=56
I have searched high and low but can not find a syntax example of how to
select based on a multi-column primary key, any tips?
Thanks,
mawrya
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] select based on multi-column primary keys
On Fri, Jan 19, 2007 at 16:44:50 -0800,
mawrya <[EMAIL PROTECTED]> wrote:
> I have set up a table with a multi-column primary key constraint:
>
> If I had a row in the table where systemid=123, enclosureid=ab,
> pointid=56, I would have a Primary Key ("ID") of 123ab56 for that row.
>
> I now want to run a select based on the Primary Key, something like:
>
> SELECT * FROM iopoints WHERE ID = 123ab56
>
> Is something like this even possible? Or am I forced to do:
>
> SELECT * FROM iopoints WHERE systemid=123 AND enclosureid=ab AND pointid=56
SELECT * FROM iopoints WHERE systemid=123 AND enclosureid='ab' AND pointid=56
While in theory you could concatenate the columns and test that against a
particular value, you probably don't want to do that. (If you do, use a
functional index.)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] select based on multi-column primary keys
--
AFAIK: You cannot have multiple primary keys. How would you know which one
is the actual key ?
FYI: What you are really talking about are table contraints... When you have
multiple unique column constraints -- they are generally referred to as
"table constraints" not multiple primary keys... Unique nmulti-column table
constraints generally use an internal set of rules and triggers and an
index.
My advice would be to alter your table structure so that you have a "real"
PK not table constraints -- that would make it searchable
There's this nifty little thing called a "sequence"... they make great
PK's BTW: If you implement a true PK... you can still retain your
UNIQUE(col1, col2, col3, ...) table constraints.
Regards,
Gregory P. Patnude
Vice President - Applications & Innovations Group
iDynaTECH, Inc
120 North Pine Street
STC - Suite 162
Spokane, WA 99202
(509) 343-3104 [voice]
http://www.idynatech.com
"mawrya" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
>I have set up a table with a multi-column primary key constraint:
>
> CREATE TABLE iopoints
> (
> enclosureid numeric(3) NOT NULL,
> pointid char(4) NOT NULL,
> equipmentgroup varchar(64) NOT NULL,
> deviceid varchar(8),
> devicetype varchar(24),
> operationdesc varchar(64) NOT NULL,
> entrytime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with
> time zone,
> systemid numeric(3) NOT NULL,
> CONSTRAINT "ID" PRIMARY KEY (systemid, enclosureid, pointid)
> )
> WITHOUT OIDS;
>
> If I had a row in the table where systemid=123, enclosureid=ab,
> pointid=56, I would have a Primary Key ("ID") of 123ab56 for that row.
>
> I now want to run a select based on the Primary Key, something like:
>
> SELECT * FROM iopoints WHERE ID = 123ab56
>
> Is something like this even possible? Or am I forced to do:
>
> SELECT * FROM iopoints WHERE systemid=123 AND enclosureid=ab AND
> pointid=56
>
> I have searched high and low but can not find a syntax example of how to
> select based on a multi-column primary key, any tips?
>
> Thanks,
>
> mawrya
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] select based on multi-column primary keys
> SELECT * FROM iopoints WHERE systemid=123 AND enclosureid='ab' AND pointid=56 A slight variation of the syntax would be: select * from iopoints where (systemid, enclosureid, pointid) = (123,'ab',56); this table and fields sounds alot like a control system be being modeled. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
