[SQL] lost on self joins

[Matthew Nuzum]

Sometimes recursion makes my head spin...

Imagine that I have a database that holds the structure of my
filesystem.  There is a table called files that contains every piece of
info you would ever want to know about a file, including a unique ID
called fileid.
 | files

x| fileid
 | filename
 | ...

Then, you have a table called folders which looks like:
 | folders
==
x| folderid
 | parentid (relates to folders.folderid)
 | foldername

Finaly, a table to allow a many to many join called files_folders
 | files_folders

x| ffid
 | folderid (fk to folders.folderid)
 | fileid (fk to files.fileid)

Now, I'd like to create a view that shows everything in files, as well
as the complete path to the file.  However because I don't know how many
levels deep the file is nested, I'm not sure how to get that complete
path.  Here is conceptually what should come out:

 | files_view
==
x| fileid
 | filename
 | ...
 | full_path

Something that won't work is: 
SELECT files.*, folders.foldername, folders2.foldername 
FROM files, folders, folders folders2, files_folders ff
WHERE files.fileid = ff.fileid 
  AND ff.folderid = folders.folderid 
  AND folders.parentid;

The problem is that files that are not in a folder won't show up, and if
a folder is more than two levels deep it will only show the two highest
levels.

Can anyone suggest a way for me to get the information I need?  I'm very
content to use a simple pl/pgsql function, however I don't know how I'd
use recursion there.

Thanks,

--
Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]




---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] lost on self joins

[Matthew Nuzum]

> -Original Message-
> From: Tomasz Myrta [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 15, 2003 3:00 PM
> To: Matthew Nuzum
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] lost on self joins
> >Finaly, a table to allow a many to many join called files_folders
> > | files_folders
> >
> >x| ffid
> > | folderid (fk to folders.folderid)
> > | fileid (fk to files.fileid)
> 
> Strange. Do you need this table? Can one file exist in several
> directories?
> If not, you can just add "folderid" field into table files.

Good point.  No, it can't exist in multiple folders, so I guess it's
overkill to do a many to many here.  Thanks for the moment of clarity.

> What would you say about this:
> 
> create or replace function parent_dir(varchar,integer) returns varchar
as
> '
> DECLARE
>   curr_name   ALIAS for $1;
>   curr_id ALIAS for $2;
>   par_namevarchar;
>   par_id  integer;
> begin
>   select into par_name,par_id foldername,parentid from folders where
> folderid=curr_id;
>   if not found or par_name is null then
> --finish
> return curr_name;
>   else
> --find upper folder
> return parent_dir(par_name || ''/'' || curr_name,par_id);
>   end if;
> end;
> ' LANGUAGE 'plpgsql';
> 
> Using:
> select parent_dir('',folderid) as fullpath...;
> or
> select parent_dir(filename,folderid) as fullfilename...;
> 
> Your query would look like this:
> SELECT files.*,
> parent_dir('',folderid) as fullfoldername
> FROM files f join files_folders ff using (fileid);

Well, I guess I would say Thanks!  You make it look so easy.

As I was planning this in my mind, I didn't have the equivalent of your
first parameter to parent_dir, but now I see it's necessary to get the
full path when you recurse up the folder hierarchy.

Thanks a lot for your response.

Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]


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

http://archives.postgresql.org

[SQL] show data from two tables together

[Matthew Nuzum]

Well, this is somewhat of a follow up to my previous post regarding self
joins.  Now what I'm hoping to do is "virtually" combine the results
from two different record sets into one apparent record set.

Here is the skeleton of my application's data structure.  There is a
table called "folders" and a table called "files".

They look like:
 | files | folders
=   
x| fileid   x| folderid
 | filename  | foldername
 | folderid  | parentid
 | dsply_order   | dsply_order

files.folderid is fk to folders.folderid, folders.parentid is field for
self joining to folderid.

Now my difficulty is that I want to see results for the two tables
together.  As you know, folders can co-exist with files inside of a
folder.  Therefore if I know my current folder is 23, then I'd like to
SELECT * FROM files WHERE files.folderid = 23 AND SELECT * FROM folders
WHERE folders.parentid = 23 and then take the combined record sets and
ORDER BY dsply_order.

The only way that I know how to do this is to do two queries and put the
results into an array and sort it outside of postgres.  However my goal
is to make life simpler for the application developers and just give
them a view that has the data they need.

As a side note, I'd probably need to add a field that would indicate 1
if the file came from files otherwise count(folders.*) WHERE parentid =
folderid so that I can see if the folder is empty.

As another side note, this operation will be performed quite frequently
and should be fast.

As I think about it, it seems that the only logical way would be to do
this at the application level, not inside postgres.  Please correct me
if I'm wrong.

--
Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]


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

http://archives.postgresql.org

Re: [SQL] show data from two tables together

[Matthew Nuzum]

Hmm... I've never used this before.  I'll try it.

Thanks for your help and your quick reply!

--
Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]
 

> Fortunately we have the set functions, specifically UNION ALL in this
> case.
> 

> Maybe something like (minus the number of files/empty part):
> 
> CREATE VIEW viewname AS
>  SELECT fileid, filename, folderid, dsply_order FROM files
>   UNION ALL
>  SELECT folderid, foldername, parentid, dsply_order FROM folders;
> 
> SELECT * from viewname where folderid=23 order by dsply_order;



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

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] cannot create function that uses variable table name

[Matthew Nuzum]

I have a number of tables in my database that use the concept of
“display order”, which is a field that can be used in an order by clause
to dictate what order the results should come out in.
 
I thought I would be crafty and devise a function that would always
return the highest numbered item in the table.  But it doesn’t work.  It
always gives me a parse error at $1.  Here’s the function:

CREATE OR REPLACE FUNCTION get_last_dsply_order(
   varchar,-- tablename
   varchar,-- id_col_name
   varchar)-- where_item
   RETURNS integer AS '
   DECLARE total_items integer;
  tablename ALIAS FOR $1;
  id_col_name ALIAS FOR $2;
  where_item ALIAS FOR $3;
   BEGIN
  SELECT INTO total_items count(*) FROM tablename WHERE id_col_name
= where_item;
   RETURN total_items;
END;
' LANGUAGE 'plpgsql';

Here’s some sample data so that you can better see what I’m doing:
Fileid| accountid | filename | dsply_order
==
 1| account1  | My File  | 1
 2| account1  | Another file | 2
 3| account1  | YA File  | 3
 4| account2  | Hello world  | 1
 5| account2  | Hi again | 2
 6| account3  | Good bye | 3
 7| account4  | Mom  | 2
 8| account4  | Dad  | 1
=
Therefore you would want to see the last item number used by account2 so
that you can add a new item to the end of the list.  You might do
something like this:
INSERT INTO files (accountid, filename, dsply_order) VALUES
(‘account2’,’Testing’,get_last_dsply_order(‘files’,’accountid’,’account2
’)); 
 
BTW, it will have a complementary trigger assigned to each table that
upon delete will shift all the items up 1 to fill in the gap left by the
deleted item.  Therefore the count() of the items in the table should
also match the highest numbered item.

--
Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]



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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Scheduling Events?

[Matthew Nuzum]

> > Yes! cron
> >
> Here is the basic problem w/ using CRON in an accounting situation.
> 
> I can't be sure that cron will always be up when the DB is up,
> so lets say crond goes down for some random reason (User, System
error,
> Etc..)
> 
> And outside adjustment is made to lets say the equipment account and
that
> adjustment was made on the value of the equipment, BUT it hadn't been
> depreciated because crond went down and no one notice.
> 
> Now I have a HUGE issue!
> 
> So I have to be sure that all entries/adjustments are made accurately
in
> the time frame they were meant to happen in.
> 

It seems that you have a good concern, so I have a suggestion.  First,
let me say that if you cannot count on cron to run your stuff at a
certain time, then you cannot count on anything to run your stuff at a
certain time.  All of your reasoning for distrusting cron is perfectly
valid in distrusting every conceivable automated system.

Therefore, you have to design your application with the assumption that
your scheduling system is untrustworthy.  If you do that, then you have
the freedom to use cron (or some other scheduling system) and build
checks into your database activities to ensure that invalid data cannot
be used if your scheduled processes did not take place.

If you don't want to make changes to existing code, then you can create
a solution as simple as a rule on your essential table(s) that first
checks to make sure the most recent scheduled task was completed
successfully and if it hasn't completed return something that the client
will understand as invalid.

If you're unfamiliar with "rules", they essentially rewrite your query
on the fly.  To quote Bruce Momjian's book, PostgreSQL: Introduction and
Concepts, "Rules allow actions to take place when a table is accessed.
In this way, they can modify the effects of SELECT, INSERT, UPDATE, and
DELETE."

I'm sure that you can think of several acceptable solutions if you learn
to distrust your data.
--
Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] changing referential integrety action on existing table

[Matthew Nuzum]

I have a table that uses the NO ACTION action for it's referential
integrity.  I'd like to change it to CASCADE for the ON DELETE event.
I'm using Postgres 7.2.

I noticed that in the output of my pg_dump I have some triggers that
look like:
CREATE CONSTRAINT TRIGGER "RI_ConstraintTriger_*"

Is it possible to use this syntax to change the constraints?  The
documentation says it's not intended for general use and isn't very
detailed about it's use.

Here is an example from my dump file:
CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_43755" AFTER DELETE ON
"packages"  NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del" ('', 'components', 'packages',
'UNSPECIFIED', 'packageid', 'packageid');

Could I simply change the procedure mentioned from RI_FKey_noaction_del
to RI_FKey_cascade_del?

Do I have to do a DROP TRIGGER first?

I know I can just try it, but last time I got creative with this, it
cost me a couple hours trying to recreate things.

Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]



---(end of broadcast)---
TIP 3: 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

[SQL] showing records from the last 20 min

[Matthew Nuzum]

I know this is such a simple question, but I can't find the answer in
the manual and I've tried very hard to find it.

I want to show all the records in a table that occurred in the last 20
min.

So, maybe something like:
select * from sys_logins WHERE tstamp >= now() - '00:20';

on pg 7.3.2 this produces the error:
ERROR:  Bad timestamp external representation '00:20'
 
Thanks for any help,
-- 
Matthew Nuzum
[EMAIL PROTECTED]
www.bearfruit.org


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

http://archives.postgresql.org

[SQL] recursive srf

[Matthew Nuzum]

Working on my first set returning function... So far the examples from
http://techdocs.postgresql.org/guides/SetReturningFunctions have worked well
for me...

I'd like to see what kind of performance I get from a particularly slow
piece of code by replacing it with a recursive srf (right now, I do the
recursion in php).

So, here's my working example, I haven't bench marked it yet, but if someone
would look at it and tell me if there's any improvements that can be made,
I'd appreciate it.  My first impression is that it's fast, because it
appeared to have returned instantaneously.  I really don't understand the
"explain analyze" output, but I'm including it as well.

I'd love to get some feedback on this (did I say that already?).

Imagine this:
CREATE TYPE nav_list AS (id int8, accountid varchar(12), 
...snip... , parent int8, subfolders int8);

subfolders is the count() of records that have their parent set to this
record's id.  I want to take a list of something like this:
home
  - item 1
  - item 2
 - sub item 1
  - item 3
and return it so that it comes out in this order
home
item1
item2
sub item 1
item 3

create or replace function nav_srf(varchar(12), int8) returns setof nav_list
as '
DECLARE 
r nav_list%rowtype;
depth int8;
last_id int8;
records RECORD;
BEGIN
FOR r IN SELECT * FROM navigation WHERE accountid = $1 AND parent =
$2 ORDER BY dsply_order LOOP
depth := r.subfolders;
last_id := r.id;
RETURN NEXT r;
IF depth > 0 THEN
FOR records IN SELECT * FROM nav_srf($1, last_id)
LOOOP
RETURN NEXT records;
END LOOP;
END IF;
END LOOP;
RETURN;
END
' LANGUAGE 'plpgsql';


# EXPLAIN ANALYZE SELECT * FROM nav_srf('GOTDNS00', 0);
QUERY PLAN
Function Scan on nav_srf  (cost=0.00..12.50 rows=1000 width=134) (actual
time=85.78..86.19 rows=22 loops=1)
Total runtime: 86.37 msec
(2 rows)

I then ran it again a moment later and got:
# EXPLAIN ANALYZE SELECT * FROM nav_srf('GOTDNS00', 0);
QUERY PLAN
Function Scan on nav_srf  (cost=0.00..12.50 rows=1000 width=134) (actual
time=23.54..23.97 rows=22 loops=1)
Total runtime: 24.15 msec
(2 rows)

BTW, this started out as a question about how to do it, but in the process
of thinking my question out, the answer came to me.  ;-)

Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]



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

http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] trigger : emulate "instead of" with before ?

[Matthew Nuzum]

I'm not a postgres expert, and I certainly don't know the details of your
situation, but it seems to me you may want to use a rule instead of a
trigger.

Then, you can intercept the delete query and simply re-write it to be an
update query that sets your deleted flag.

--
Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]
 
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-sql-
> [EMAIL PROTECTED] On Behalf Of Albrecht Berger
> Sent: Friday, June 13, 2003 7:51 AM
> To: pgsql
> Subject: [SQL] trigger : emulate "instead of" with before ?
> 
> Hello,
> I need a trigger which is updating a delete flag of a row instead of
> deleting it physically.
> 
> How do I implement a trigger which doesn't execute a delete without
> raising
> an exception
> in plsql ?
> 
> CREATE TRIGGER trigger1 BEFORE DELETE ON table1
> FOR EACH ROW EXECUTE PROCEDURE setDeleteFlag ();
> 
> If I use
> RAISE EXCEPTION "..."
> the delete statement isn't executed, but I think the transaction is rolled
> back too, am I right ?
> 
> So what is the best way to implement something (Oracle like) like that :
> CREATE TRIGGER trigger1 INSTEAD OF DELETE ON table1
> FOR EACH ROW EXECUTE PROCEDURE setDeleteFlag ();
> 
> thx
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html


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

http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] most efficient way to manage ordering

[Matthew Nuzum]

Sorry if this is confusing, it is somewhat difficult to explain.

I find myself frequently creating solutions to the same problem.  I'm not
yet happy with the way I've done any of them and I'd like to find a purely
SQL way of doing this if possible.

Here's what I have.  For a contrived illustration, let's say we have a
database of photo galleries, each having some number of images.  Our tables
would look like this:

galleries
-
galleryid   | int4 (pkey)
name| text


images
-
imageid | int4 (pkey)
galleryid   | int4 (fkey)
image   | text
dsply_order | int4 (index)


Now, the same database holds many different galleries.  Each gallery has
some number of images and the users want the images to show in a certain
order. This is done by inserting the images in the order you want them to
appear.  However, it may be necessary for the user to re-order them.  I
provide a MOVE UP, MOVE DOWN, MOVE TO TOP and MOVE TO BOTTOM option so that
they can change the order.  Also, people occasionally delete images.

If a person maintaining galleryid 1 which has 6 images, a "SELECT * FROM
images WHERE galleryid = 1 ORDER BY dsply_order" might show this:
imageid | galleryid | image  | dsply_order
+---+--+
4 | 1 | 1/me.gif | 1
7 | 1 | 1/aa.gif | 2
12| 1 | 1/bb.gif | 3
11| 1 | 1/cc.gif | 4
15| 1 | 1/dd.gif | 5
18| 1 | 1/ee.gif | 6

Now, when a person decide to re-order, it's no problem to do this:
To move imageid 12 to the top:
UPDATE images SET dsply_order = CASE WHEN imageid = 12 THEN 1 ELSE
dsply_order + 1 END WHERE galleryid = 1;

That however leaves a gap at dsply_order 4: 
imageid | galleryid | image  | dsply_order
+---+--+
12| 1 | 1/bb.gif | 1
4 | 1 | 1/me.gif | 2
7 | 1 | 1/aa.gif | 3
11| 1 | 1/cc.gif | 5
15| 1 | 1/dd.gif | 6
18| 1 | 1/ee.gif | 7

Similar problem occurs when someone deletes an item.

Ideally, I'd like to figure out a single SQL query that can be run
afterwards to clean up the dsply_order to make sure that each number occurs
only one time and that there are no gaps.

I know I can write a sp for this, but the problem is, I do this very task on
lots of different tables that all have different formats and different types
of data.  I'd rather not have to maintain many different procedures if I can
find an alternate.

Right now, I get the job done in code, but it feels inefficient.

Matthew Nuzum   | ISPs: Make $200 - $5,000 per referral by
www.followers.net   | recomending Elite CMS to your customers!
[EMAIL PROTECTED]   | http://www.followers.net/isp




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

[SQL] matching rows differing only by fkey,pkey

[Matthew Nuzum]

I'm duplicating some fields in the table but the duplicates will have a new
primary key and a new foreign key.  For example,

Table "b" looks like this:
bid (pkey default value is a sequence)
aid   (fkey)
field1
field2
field3

INSERT INTO b (aid, field1, field2, field3) 
SELECT 23, field1, field2, field3 from b where aid = 22;

"b" is the middle table of a many to many relationship.  The end result is
to duplicate the data for a particular record in table "a" so that all of
it's related data in tables "b" and "c" is duplicated.

When the relationships are one to one or one to many this process is easy,
however sometimes there's a many to many relationship.

It seems that a helpful tool would be a query that can return just the pkey
of the original record copied from and the pkey of the newly created record.

For example, if the b table looked like this after a copy of 3 rows:
bid | aid | field1 | field2 | field3
1   | 22  | abc| 123| abc123
2   | 22  | xyz| 456| xyz456
3   | 22  | pdq| 789| pdq789
4   | 23  | abc| 123| abc123
5   | 23  | xyz| 456| xyz456
6   | 23  | pdq| 789| pdq789

I'd like to get this:
oldbid | newbid
1  | 4
2  | 5
3  | 6

Any ideas? If someone has an alternate method of achieving the same result
I'd be excited to hear about it.

Matthew Nuzum   | ISPs: Make $200 - $5,000 per referral by
www.followers.net   | recomending Elite CMS to your customers!
[EMAIL PROTECTED]   | http://www.followers.net/isp



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] matching rows differing only by fkey,pkey

[Matthew Nuzum]

Thanks for your suggestion to use curval(), that will be useful.  I'd
entirely forgotten about that function.  That likely will help when combined
with Richard's suggestion and an idea I already had.

I'm sorry that the problem wasn't clearer.  The best way I can describe it
is like this:
  The end result is to duplicate the data for a particular record in table
  "a" so that all of it's related data in tables "b" and "c" is duplicated.
Where "b" is the middle table in a many to many relationship.  For example,

- a - b - c 
 aid  <--+   bid  <--+   cid
 data1   +-->aid +-->bid
 data2   field1  info1
 date3   field2  info2

SELECT a.*,b.*,c.* from a,b,c where b.aid = a.aid and c.bid = b.bid;

So, the goal is to duplicate an object that is made up of the data stored
across these three tables. 

Any suggestions?

Matthew Nuzum   | ISPs: Make $200 - $5,000 per referral by
www.followers.net   | recomending Elite CMS to your customers!
[EMAIL PROTECTED]   | http://www.followers.net/isp




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

   http://www.postgresql.org/docs/faqs/FAQ.html