Re: [SQL] connectby documentation

[Jean-Paul Argudo]

Daniel Caune a écrit :
> Wow, that was the quest for the Holy Grail! :-)

Yes I understand. That kind of documentation for a
contrib-addon-whatever for PostgreSQL can be tricky sometimes to find..


I just jump on that thread to place a reminder for all those wanting to
implement trees in databases, just in case they are still thinking about
howto do that.

I wroted an article on that topic (in french only sorry :
http://www.postgresqlfr.org/?q=node/142), where I compare id/parent_id,
nested loops and Miguel Sofer's method.

This method is explained on OpenACS forums (in english)

http://openacs.org/forums/message-view?message_id=18365

The original work of Miguel Sofer (with a PostgreSQL implementation as
an example) can be found here:

http://www.utdt.edu/~mig/sql-trees/

Be sure to download the tar.gz. on the like "here"... and read his draft.

I'm really convinced this method is the best so far. I used it in 3
different projects where I had to implement big trees structures on a
table. They all still work with no problem of any kind.

Just to let you know in case you missed that ;-)

My 2 ¢

--
Jean-Paul Argudo
www.Argudo.org
www.PostgreSQLFr.org

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] PL/PGSQL - How to pass in variables?

[Jean-Paul Argudo]

Hi Scott,

You'll have to execute dynamic SQL (see doc chapter "36.6.5. Executing
Dynamic Commands") for your function to work:

CREATE FUNCTION get_table_count(tablename text) RETURNS integer AS
$$
DECLARE
--tablename ALIAS FOR $1;
 rowcount INTEGER;
  BEGIN

execute 'SELECT count(*) FROM '||tablename into rowcount;
return rowcount;

  END;
$$ LANGUAGE 'plpgsql';


select get_table_count('bar');

 get_table_count
-----
       3
(1 row)




Cheers,

-- 
Jean-Paul Argudo
www.PostgreSQLFr.org
www.dalibo.com

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] PL/PGSQL - How to pass in variables?

[Jean-Paul Argudo]

Scott Yohonn wrote:
> Jean-Paul,
> 
> Thanks! This did work. The output put the name of the function
> (get_table_count) as the header. How would I display the name of the table
> that I am requesting the row count of?

The only way I know is to alias the output in the query calling the
function, so:


select get_table_count('bar') as bar;

 bar
-
   3
(1 row)

I don't know any other way to do that...

Cheers,

-- 
Jean-Paul Argudo
www.PostgreSQLFr.org
www.dalibo.com

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [SQL] age() vs. timestamp substraction

[Jean-Paul Argudo]

Hi all,

> Where did you get that idea?  age's reference point is current_date (ie,
> midnight) not now().  There are also some differences in the calculation
> compared to a plain timestamp subtraction.

I'm jumping on this thread to point out a little strange thing to me.
CURRENT_DATE, converted (stupidly) as a string *with* hour is "current
date at mid-day":


test=# select to_char(current_date,'-MM-DD HH:MI:SS');
   to_char
-
 2006-10-06 12:00:00
(1 ligne)

It was a day when I had to debug a strange behaviour in a customer's
(bad) code :-)

Cheers,

-- 
Jean-Paul Argudo
www.PostgreSQLFr.org
www.dalibo.com

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org