Re: [HACKERS] 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(at)gmail(dot)com>

> 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(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/

On Fri, Dec 18, 2009 at 10:57 AM, suzhiyang <suzhiyang(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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")

> 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

PS. suzhiyang, please use "Reply All" when talking on this list.

2009/12/19 Filip Rembiałkowski <plk(dot)zuber(at)gmail(dot)com>:
>
> W dniu 19 grudnia 2009 03:20 użytkownik suzhiyang <suzhiyang(at)gmail(dot)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(at)systemguards(dot)com(dot)ec> napisał:

>
> 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(dot)rembialkowski(at)gmail(dot)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(at)systemguards(dot)com(dot)ec> napisał:

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(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/