Re: [SQL] Joins involving functions

[Markus Schaber]

Hi, Jeff Boes,

Jeff Boes wrote:

> select * from bar
> cross join f_foo(bar.b) as foo;
> 
> Unfortunately, this last statement returns an error:
> 
> NOTICE:  adding missing FROM-clause entry for table "bar"
> ERROR:  function expression in FROM may not refer to other relations of
> same query level
> 
> Is there a method for joining function calls to tables? The only other
> approach I can think of is to write another function that returns "setof".

I don't exactly know what you want to achieve, so I'm just wildly guessing.

Do you really want the cross join between bar.* and f_foo(bar.b)?

If yes, use

SELECT x.b, y.b FROM bar x CROSS JOIN bar y;

If not, you maybe want

SELECT b, f_foo(b) FROM bar;

Or it may be you want something like:

SELECT * from bar CROSS JOIN (SELECT f_foo(bar.b) from bar) as foo ;


HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] how to use recursion to find end nodes of a tree

[mike]

Thankyou very much Yasir and Ross for your help and advice. 
I have created a pl/pgsql version of Yasirs algorithm which
works perfectly, I am also looking into improving efficiency 
by flaging leaf records. Here is my pl/pgsql solution in case
it helps anyone out:

CREATE OR REPLACE FUNCTION parentchildtest(int4)
  RETURNS _int4 AS
'DECLARE
node ALIAS FOR $1; 
s INTEGER[];
leaves INTEGER[];
top INTEGER;
counter INTEGER;
leaf_id INTEGER; 
popped INTEGER;
child RECORD;
childCount RECORD;
BEGIN
leaf_id := 0;
top := 0;
s := ''{}'';
leaves := ''{}'';   
s[top] := node;
counter := 1;


-- t a depth first search
WHILE (counter <> 0) LOOP
popped := s[top];
top := top - 1;
counter := counter - 1;

FOR child IN SELECT pc.child_id FROM parent_child AS pc
WHERE pc.parent_id = popped
LOOP
SELECT INTO childCount COUNT(*) AS count FROM parent_child AS pc
WHERE pc.parent_id = child.child_id;

--a count of zero indicates that child node has no children
IF (childCount.count = 0) THEN  
leaves[leaf_id] = child.child_id;
leaf_id := leaf_id + 1;
ELSE
-- not a leaf, so add it to the stack for the next time
through
-- the loop
top := top + 1;
s[top] = child.child_id;
counter := counter + 1;
END IF;
END LOOP;
END LOOP;

RETURN leaves;
END;'
  LANGUAGE 'plpgsql' VOLATILE;  

-Original Message-
From: Yasir Malik [mailto:[EMAIL PROTECTED] 
Sent: 10 April 2006 17:13
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] how to use recursion to find end nodes of a tree


> Hello All,
>
> I have been having a really hard time trying to come up with a 
> pl/pgsql recursive function to returns the end nodes of a tree. Here 
> is an example table definition:
>
> CREATE TABLE parent_child (
> parent_id integer NOT NULL,
> child_id integer NOT NULL
> );
>
> INSERT INTO parent_child (parent_id, child_id) VALUES (1, 2); INSERT 
> INTO parent_child (parent_id, child_id) VALUES (1, 3); INSERT INTO 
> parent_child (parent_id, child_id) VALUES (1, 4); INSERT INTO 
> parent_child (parent_id, child_id) VALUES (2, 5); INSERT INTO 
> parent_child (parent_id, child_id) VALUES (2, 6); INSERT INTO 
> parent_child (parent_id, child_id) VALUES (4, 7); INSERT INTO 
> parent_child (parent_id, child_id) VALUES (4, 8); INSERT INTO 
> parent_child (parent_id, child_id) VALUES (4, 9); INSERT INTO 
> parent_child (parent_id, child_id) VALUES (9, 10);
>
> This produces the following tree of data:
>
>   1
>___|___
>   |   |   |
>   2   3   4
>  _|_ _|_
> |   |   | | |
> 5   6   7 8 9
> |
> 10
>
> I want to create a function that returns the terminating nodes of of 
> this tree below a certain level i.e. if I input 1 to the function I 
> need it to return 5,6,3,7,8,10. If I input 4 to the function I would 
> get 7,8,10. I have written recursive functions which return all nodes 
> on a branch of a tree but I can't think of a way to return the end 
> nodes does anyone know of a solution?
>
I haven't programmed in PL/pgSQL in a while, but I'll write some pseudo 
code.  I think the code should be similar:

func(int node)
{
dynamic_array s;
dynamic_array leaves;
int top, count, leaf_id, popped, child;

leaf_id = top = 0;
s[top] = node;
count = 1;

// to a depth first search
while(count != 0)
{
  popped = s[top];
  top--;
  count--;

  foreach(select pc.child_id into child from parent_child pc where
  pc.parent_id = popped)
  {
 select * from parect_child pc where parent_id = child;

 // a count of zero indicates that child node has no children
 if(count_of_above_query = 0)
 {
   leaves[leaf_id] = child;
   leaf_id++;
 }
 else
 {
// not a leaf, so add it to the stack for the next time through
// the loop
top++;
s[top] = child;
count++;
 }
  }
}

return leaves;
}

Regards,
Yasir





---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] slow 'IN' clause

[Bruno Wolff III]

On Sun, Apr 09, 2006 at 20:44:34 -0700,
  [EMAIL PROTECTED] wrote:
> I have a slow sql:
> SELECT * FROM mytable WHERE id IN (1,3,5,7,3k here...);
> mytable is about 10k rows.
> 
> if don't use the "IN" clause, it will cost 0,11 second, otherwise it
> will cost 2.x second
> I guess pg use linear search to deal with IN clause, is there any way
> to let pg use other search method with IN clause? (ex.Binary Search or
> hash Search)

It would help if you told us what version of Postgres you are running
and showed EXPLAIN ANALYZE results for the two queries.

I have heard of people getting better speed by loading the constant list
into a temp table and then doing a join.

Some older versions of Postgres also had problems with large constant lists,
so if you are using an old version of Postgres upgrading might help.

---(end of broadcast)---
TIP 6: explain analyze is your friend