Обсуждение: Sort a column that does not exist

Hi,<br /><br />I'm in a project that probably some of you have heart about, it is called PostgreSQLf and I get some
helpbefore from this list. The developer team is been doing some progress about. Now one of us has created an extra
columnthat is called membership degree, this performs some calculations over some data that it is store on a data
table.We just create a target entry on the parser and the planner, then in the ExecScan function is where the
calculationsperforms.<br /><br />Well, I have been assigned a task. I have to sort that target entry. I created this
targetentry on parse_clause and then I do this:<br /><br />List *<br />transformSortClause(ParseState *pstate,<br />   
               List *orderlist,<br />                     List **targetlist,<br />                    bool
resolveUnknown)<br/>{<br />    List       *sortlist = NIL;<br />    ListCell   *olitem;<br /><br />    foreach(olitem,
orderlist)<br/>    {<br />        SortBy       *sortby = lfirst(olitem);<br />         TargetEntry *tle;<br />       
//Tofind out if it is the membership degree<br />        char       *namegrmemb = strVal(linitial(((ColumnRef *)
sortby->node)->fields));<br/>        <br />        if (strcmp(namegrmemb, "grmemb")==0)<br />             tle =
createTargetFuzzyEntry(targetlist);<br/>        else<br />            tle = findTargetlistEntry(pstate,
sortby->node,<br/>                                      targetlist, ORDER_CLAUSE);<br /><br />        sortlist =
addTargetToSortList(pstate,tle,<br />                                        sortlist, *targetlist,<br />           
                          sortby->sortby_dir,<br />                                      
sortby->sortby_nulls,<br/>                                       sortby->useOp,<br />                            
          resolveUnknown);<br />        <br />        <br /><br />    }<br /><br />    return sortlist;<br />}<br /><br
/>//Tosort the membership degree<br />TargetEntry *<br />createTargetFuzzyEntry(List **targetlist){<br />     <br />   
/*Ijust have to create the fuzzy target entry right here */<br />    TargetEntry    *tfp = makeNode(TargetEntry);<br
/>   Const   *cn = makeNode(Const);<br />    float    val = 1.0;<br />    TargetEntry *tlast = list_nth(*targetlist,
list_length(*targetlist)-1);<br/><br />    cn = makeConst(700, -1, 4, (Float4GetDatum(val)), false, true); <br />   
tfp->resorigtbl=tlast->resorigtbl;<br/>    tfp->expr = (Expr *) cn;<br />    tfp->resno =
list_length(*targetlist)+ 1;<br />    tfp->resname = "grmemb";<br />     tfp->resorigcol =
list_length(*targetlist)+ 1;<br />    tfp->ressortgroupref = 0;<br />    tfp->resjunk = false;<br /><br />   
*targetlist= lappend(*targetlist, tfp);<br /><br />    return tfp;<br />}<br />As you can see if someone do this:
SELECT* FROM table WHERE field=some_value ORDER BY grmemb, postgresql creates a new target entry and then assigned to
thetargetlist as a sort node. I know that it creates the node on the parser, but it does not work, it seems the
executordon't see it.<br /><br />How could I sort a column like this?, I know i'm missing something, but i just don't
seeit. What is the process to sort a column?<br /> 

2009/4/1 Werner Echezuria <wercool@gmail.com>:
> As you can see if someone do this: SELECT * FROM table WHERE
> field=some_value ORDER BY grmemb, postgresql creates a new target entry and
> then assigned to the targetlist as a sort node. I know that it creates the
> node on the parser, but it does not work, it seems the executor don't see
> it.

See include/nodes/primnodes.h around line 1075:bool        resjunk;        /* set to true to eliminate the attribute
from                            * final target list */
 

If the TargetEntry is set resjunk = false, the final result is
filtered as junk. So more accurately the executor sees but drops it.

>
> How could I sort a column like this?, I know i'm missing something, but i
> just don't see it. What is the process to sort a column?
>

Use makeTargetEntry in makefuncs.c
TargetEntry *
makeTargetEntry(Expr *expr,            AttrNumber resno,            char *resname,            bool resjunk)

by the 4th argument you can set resjunk = false if you don't want it
to be in the result.

Regards,


-- 
Hitoshi Harada

<br />Hi, the problem goes on. I think the problem is in the planner.c on grouping_planner function, because when I do
aregular sort it gets to it:<br /><br />    /*<br />     * If we were not able to make the plan come out in the right
order,add<br />      * an explicit sort step.<br />     */<br />    if (parse->sortClause)<br />    {<br />       
if(!pathkeys_contained_in(sort_pathkeys, current_pathkeys))<br />        {<br />            result_plan = (Plan *)
make_sort_from_pathkeys(root,<br/>                                                            result_plan,<br />       
                                                  sort_pathkeys,<br />                                               
          limit_tuples);<br />             current_pathkeys = sort_pathkeys;<br />        }<br />    }<br /><br />and
dothe make_sort_from_pathkeys, but when I do the sort by grmemb it does not. So I change it in order to pass through
make_sort_from_pathkey,but it drops an error like this "invalid attnum", so when I go to heaptuple and force to get to
ObjectIdGetDatumin heap_getsysattr, the server hang out. What can I do? How can I assign a valid attrnum?<br /><br
/><br/><div class="gmail_quote">2009/4/1 Hitoshi Harada <span dir="ltr"><<a
href="mailto:umi.tanuki@gmail.com">umi.tanuki@gmail.com</a>></span><br/><blockquote class="gmail_quote"
style="border-left:1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> 2009/4/1 Werner
Echezuria<<a href="mailto:wercool@gmail.com">wercool@gmail.com</a>>:<br /><div class="im">> As you can see if
someonedo this: SELECT * FROM table WHERE<br /> > field=some_value ORDER BY grmemb, postgresql creates a new target
entryand<br /> > then assigned to the targetlist as a sort node. I know that it creates the<br /> > node on the
parser,but it does not work, it seems the executor don't see<br /> > it.<br /><br /></div>See
include/nodes/primnodes.haround line 1075:<br />        bool            resjunk;                /* set to true to
eliminatethe attribute from<br />                                                                 * final target list
*/<br/><br /> If the TargetEntry is set resjunk = false, the final result is<br /> filtered as junk. So more accurately
theexecutor sees but drops it.<br /><div class="im"><br /> ><br /> > How could I sort a column like this?, I know
i'mmissing something, but i<br /> > just don't see it. What is the process to sort a column?<br /> ><br /><br
/></div>UsemakeTargetEntry in makefuncs.c<br /> TargetEntry *<br /> makeTargetEntry(Expr *expr,<br />                  
            AttrNumber resno,<br />                                char *resname,<br />                              
 boolresjunk)<br /><br /> by the 4th argument you can set resjunk = false if you don't want it<br /> to be in the
result.<br/><br /> Regards,<br /><br /><br /> --<br /><font color="#888888">Hitoshi Harada<br
/></font></blockquote></div><br/>