Обсуждение: Distinguish view and table problem
Hi!
I just want to distinguish a view and a table while postgres execute exec_simple_query(). In the systable of pg_class, a view and a table has different relkind ('r' 'v'). But when I print the parsetree and the rewrite parsetree, I found that a view and a table has no character to distinguish because the structure Relation has no attribute called relkind. Maybe I should read systable to confirm that we are select from a view or table? But there's still has problem. How could I get the relkind of a table(view) by its name from pg_class?
Another question is that does postgres save the user's original query_string at anywhere(systable etc.)? If I want to save the sourceText in the systable, I could add a column to pg_class called query_string. How could I insert a line to pg_class or read a line from it?
Thank you very much!
2009-12-18
suzhiyang
2009/12/18 suzhiyang <suzhiyang@gmail.com>
pg_class is (quite logically) UNIQUE on (relname, relnamespace)
SELECT c.relkind from pg_class c, pg_namespace n
where c.relnamespace = n.oid
and c.relname = 'thetable'
and n.nspname = 'theschema'
How could I get the relkind of a table(view) by its name from pg_class?
pg_class is (quite logically) UNIQUE on (relname, relnamespace)
SELECT c.relkind from pg_class c, pg_namespace n
where c.relnamespace = n.oid
and c.relname = 'thetable'
and n.nspname = 'theschema'
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
On Fri, Dec 18, 2009 at 10:57 AM, suzhiyang <suzhiyang@gmail.com> wrote: > Another question is that does postgres save the user's original query_string > at anywhere(systable etc.)? If I want to save the sourceText in the > systable, I could add a column to pg_class called query_string. How could I > insert a line to pg_class or read a line from it? pg_class wouldn't make much sense for this. But you might be interested in pg_stat_activity. ...Robert
"suzhiyang" <suzhiyang@gmail.com> writes: > I just want to distinguish a view and a table while postgres execute > exec_simple_query(). In the systable of pg_class, a view and a table > has different relkind ('r' 'v'). But when I print the parsetree and > the rewrite parsetree, I found that a view and a table has no > character to distinguish because the structure Relation has no > attribute called relkind. See rel->rd_rel->relkind ... regards, tom lane
W dniu 19 grudnia 2009 03:20 użytkownik suzhiyang <suzhiyang@gmail.com> napisał:
Are you programming in C? If so, use the API provided by PostgreSQL,
http://www.postgresql.org/docs/current/static/libpq.html
PS. suzhiyang, please use "Reply All" when talking on this list.
Sorry, I've not describe my problem precisely.I mean that I want to get relkind or something from a systable by the programm but not by sql.
I don't understand how you can get data from table without using SQL. (maybe I'm just "too sql")
That is, if I execute such sql by exec_simple_query("select xxx from pg_xxx"), how could I get the result of it in the programm?
Are you programming in C? If so, use the API provided by PostgreSQL,
http://www.postgresql.org/docs/current/static/libpq.html
2009/12/19 Filip Rembiałkowski <plk.zuber@gmail.com>: > > W dniu 19 grudnia 2009 03:20 użytkownik suzhiyang <suzhiyang@gmail.com> > napisał: >> >> Sorry, I've not describe my problem precisely. >> I mean that I want to get relkind or something from a systable by the >> programm but not by sql. > > I don't understand how you can get data from table without using SQL. (maybe > I'm just "too sql") > i think he is hacking postgres's source code to make the TODO: "allow recompilation of views" (he send an email about that in another thread)... i think this is somewhat necesary to read: http://wiki.postgresql.org/wiki/Developer_FAQ#Technical_Questions and of course look at other files that acces that kind of info, for example look at AlterTableNamespace() funtion in src/backend/commands/tablecmds.c to find out for a complete example to identify tables and views -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
W dniu 19 grudnia 2009 16:19 użytkownik Jaime Casanova <jcasanov@systemguards.com.ec> napisał:
oh. I didn't realise, that such seemingly simple question can relate to such hard task.
even Oracle and other big players do not have an ideal solution for this...
good luck suzhiyang!
i think he is hacking postgres's source code to make the TODO: "allow
recompilation of views" (he send an email about that in another
thread)...
oh. I didn't realise, that such seemingly simple question can relate to such hard task.
even Oracle and other big players do not have an ideal solution for this...
good luck suzhiyang!
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
.........
This task is just a homework for me, but the TA may not deep into this problem and give me such difficult task. That simple idea was very ugly by all appearances. I'm a freshman to postgres, sorry for that bad idea. Now I've give up this problem, complaint to TA and try to solve another easier one to complete my work.
These days I've learned a lot from your discussion and source code.:-)
Thank you for your help!
2009-12-20
suzhiyang
发件人: Filip_Rembiałkowski
发送时间: 2009-12-20 08:33:31
收件人: Jaime Casanova
抄送: suzhiyang; Pgsql Hackers
主题: Re: [HACKERS] Distinguish view and table problem
W dniu 19 grudnia 2009 16:19 użytkownik Jaime Casanova <jcasanov@systemguards.com.ec> napisał:
oh. I didn't realise, that such seemingly simple question can relate to such hard task.
even Oracle and other big players do not have an ideal solution for this...
good luck suzhiyang!
i think he is hacking postgres's source code to make the TODO: "allow
recompilation of views" (he send an email about that in another
thread)...
oh. I didn't realise, that such seemingly simple question can relate to such hard task.
even Oracle and other big players do not have an ideal solution for this...
good luck suzhiyang!
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/