Thread: Creating a Pseudocolumn
Hey everyone, I'm reworking hierarchical queries and am adding a LEVEL pseudocolumn.As it's a totally calculated attribute, what's thebest way to handle it keeping in mind that LEVEL is only used in a hierarchical query? Looking at Evgen's patches, if he recognizes a hierarchical query and finds a LEVEL column in the target list, he creates a FakeVar (similar to Var) entry for it which is then passed throughout the system and modified on a per-tuple basis. While this works, it seems a bit kludgy and requires a good amount of special-case code. As such, I was wondering if you guys have some suggestions on how to use what's already there to accomplish the same thing. I couldn't think of any pseudocolumns like this in PostgreSQL, but I may just be brain-dead again. As it may be sorta similar, how were we discussing handling rownum? -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On Mon, May 15, 2006 at 11:03:46AM -0400, Jonah H. Harris wrote: > I couldn't think of any pseudocolumns like this in PostgreSQL, but I > may just be brain-dead again. As it may be sorta similar, how were we > discussing handling rownum? tableoid is a pseudo-column like you mean, perhaps you should look how that works. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On 5/15/06, Martijn van Oosterhout <kleptog@svana.org> wrote: > tableoid is a pseudo-column like you mean, perhaps you should look how > that works. I thought tableoid was a system column with a physical representation on the tuple itself? I don't want any on-disk representation of my pseudocolumn... just assigned at runtime similar to rownum. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/
On Mon, May 15, 2006 at 11:17:41AM -0400, Jonah H. Harris wrote: > On 5/15/06, Martijn van Oosterhout <kleptog@svana.org> wrote: > >tableoid is a pseudo-column like you mean, perhaps you should look how > >that works. > > I thought tableoid was a system column with a physical representation > on the tuple itself? I don't want any on-disk representation of my > pseudocolumn... just assigned at runtime similar to rownum. Tableoid is certainly not stored on disk (waste of space) but retreived specially whenever someone asks for the tableoid column. See heap_getsysattr in access/common/heaptuple.c. All you need to do is decide where you are going to store the level number and add it as a system attribute (negative attribute number). Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout <kleptog@svana.org> writes:
> All you need to do is decide where you are going to store the level
> number and add it as a system attribute (negative attribute number).
This could only work if LEVEL is guaranteed to have one and only one
value per tuple. I'm not too sure about the spec but it seems like
that'd probably fall down in join situations.
regards, tom lane
On 5/15/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > This could only work if LEVEL is guaranteed to have one and only one > value per tuple. I'm not too sure about the spec but it seems like > that'd probably fall down in join situations. Yes, this was another thing handled by Evgen's patch... FakeVar could only exist once and was ignored in joins. So, basically (like rownum), I have to create a pseudocolumn that isn't bound by joins. Do you know of any way around this? -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/ "SQL will be the COBOL in the year 2020" -Mike Stonebraker
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> I'm reworking hierarchical queries and am adding a LEVEL pseudocolumn.
> As it's a totally calculated attribute, what's the best way to handle
> it keeping in mind that LEVEL is only used in a hierarchical query?
Perhaps you should start by explaining what the heck you're talking
about ;-). I can find nothing in the SQL2003 spec that sounds anything
like a LEVEL function or pseudocolumn.
regards, tom lane
I think Jonah is referring to the the 'START WITH ... CONNECT BY' clause feature from Oracle. Am I right Jonah? For such queries, Oracle introduces a pseudocolumn LEVEL, that holds the value of the indentation level of the current rusultant row. In Oracle, the LEVEL column returns 0 for the root node of a hierarchy, 1 for it's children, 2 for their children, and so forth. LEVEL is commonly used to indent hierarchical results. LEVEL might not be a part of the standard, but it is very handy when dealing with hierarchical queries. The chapter 1 (http://www.oreilly.com/catalog/sqlpr/chapter/ch01.pdf) of book 'SQL Pocket Guide' elaborates more on it. Gurjeet. On 5/16/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Perhaps you should start by explaining what the heck you're talking > about ;-). I can find nothing in the SQL2003 spec that sounds anything > like a LEVEL function or pseudocolumn. >
On Tue, May 16, 2006 at 03:18:19PM +0530, Gurjeet Singh wrote: > LEVEL might not be a part of the standard, but it is very handy > when dealing with hierarchical queries. > > The chapter 1 (http://www.oreilly.com/catalog/sqlpr/chapter/ch01.pdf) > of book 'SQL Pocket Guide' elaborates more on it. Looking at that text, it describes how to generate a LEVEL value using the WITH RECURSIVE method, it seems to me you could apply the same method to what you're doing. Just like how on UPDATE and DELETE queries an invisible "ctid" column is added, LEVEL would be an attribute of the tuple being passed up. I'm assuming that what's actually being implemented is the SQL standard method with the Oracle alternative being another way of specifying the same thing? Have a ncie day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout <kleptog@svana.org> writes:
> I'm assuming that what's actually being implemented is the SQL standard
> method with the Oracle alternative being another way of specifying the
> same thing?
What's being implemented should be the standard. Full stop.
regards, tom lane
On 5/16/06, Martijn van Oosterhout <kleptog@svana.org> wrote: > I'm assuming that what's actually being implemented is the SQL standard > method with the Oracle alternative being another way of specifying the > same thing? I'm working on both versions of hierarchical queries; CONNECT BY for EnterpriseDB and WITH [RECURSIVE] for PostgreSQL. Currently, they're completely separate implementations, but in a few months I should be able to translate CONNECT BY into actual WITH syntax. Anyway, I'm working on the CONNECT BY version and am looking at how best to implement the level pseudocolumn. In the pseudocolumn respect, this is somewhat similar to the rownum discussion. I've added a system attribute to handle level, and it does work. But, I'm thinking there's going to be a couple gotcha's hidden in there somewhere. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation | fax: 732.331.1301 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com Iselin, New Jersey 08830 | http://www.enterprisedb.com/