Thread: Creating a Pseudocolumn

Creating a Pseudocolumn

From
"Jonah H. Harris"
Date:
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/


Re: Creating a Pseudocolumn

From
Martijn van Oosterhout
Date:
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.

Re: Creating a Pseudocolumn

From
"Jonah H. Harris"
Date:
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/


Re: Creating a Pseudocolumn

From
Martijn van Oosterhout
Date:
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.

Re: Creating a Pseudocolumn

From
Tom Lane
Date:
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


Re: Creating a Pseudocolumn

From
"Jonah H. Harris"
Date:
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


Re: Creating a Pseudocolumn

From
Tom Lane
Date:
"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


Re: Creating a Pseudocolumn

From
"Gurjeet Singh"
Date:
    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.
>


Re: Creating a Pseudocolumn

From
Martijn van Oosterhout
Date:
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.

Re: Creating a Pseudocolumn

From
Tom Lane
Date:
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


Re: Creating a Pseudocolumn

From
"Jonah H. Harris"
Date:
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/