Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On 2/3/06, Tyler MacDonald <[EMAIL PROTECTED]> wrote:
> > I've been wondering, does anybody know which is more likely to be
> > installed on a postgresql server? Which is faster? I'm writting an
> > application in perl that is going to need to get broad information about
> > heiarchial data (how many parents, settings common on parents, etc), and I'd
> > like to put that data presentation logic into the database.
>
> plpgsql.
>
> for an explanation of my reasoning and a example of how to deal with
> heiarchial data, check out my blog at
> http://people.planetpostgresql.org/merlin/
Awesome, thanks! I'm using plpgsql now and am slowly getting
comfortable with it. I've attached my first draft of the recursive flag
setting finder; it works, but it's inefficient. I've got a postgres guru
here helping me out and I'll be sure to read over your article as well.
Cheers,
Tyler
CREATE OR REPLACE FUNCTION
aus_find_all_user_flags(integer, integer)
RETURNS
integer
AS '
DECLARE
member_of_row RECORD; --
BEGIN
FOR member_of_row IN
SELECT member_of FROM aus_user_membership WHERE user_id = $2
LOOP
PERFORM aus_find_all_user_flags($1, member_of_row.member_of); --
END LOOP; --
PERFORM aus_find_user_flags($1, $2); --
RETURN 1; --
END; --
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION
aus_find_user_flags (integer, integer)
RETURNS
integer
AS '
DECLARE
flag_row RECORD; --
flag_row_switch TEXT; --
flag_row_name TEXT; --
BEGIN
FOR flag_row IN
SELECT * FROM aus_user_flags WHERE user_id = $2
LOOP
flag_row_switch := (
CASE WHEN
flag_row.enabled
THEN
''true''
ELSE
''false''
END
); --
flag_row_name := quote_literal(flag_row.flag_name); --
EXECUTE ''
INSERT INTO tmp_all_user_flags (user_id, flag_name)
SELECT '' || $1 || '', '' || flag_row_name || ''
WHERE TRUE EXCEPT
SELECT user_id, flag_name
FROM tmp_all_user_flags; --
''; --
EXECUTE ''
UPDATE tmp_all_user_flags SET enabled = '' || flag_row_switch
|| ''
WHERE user_id = '' || $1 || '' AND flag_name = ''
|| flag_row_name || ''; --
''; --
END LOOP; --
RETURN 1; --
END; --
' LANGUAGE plpgsql;
CREATE TYPE aus_all_user_flag_set AS
(user_id int, flag_name varchar(128), enabled bool);
CREATE OR REPLACE FUNCTION
aus_all_user_flags (integer)
RETURNS
SETOF aus_all_user_flag_set
AS '
DECLARE
rec aus_all_user_flag_set%ROWTYPE; --
BEGIN
CREATE TEMPORARY TABLE tmp_all_user_flags (
user_id INT NOT NULL,
flag_name VARCHAR(128) NOT NULL,
enabled BOOL NULL
) ON COMMIT DROP; --
PERFORM aus_find_all_user_flags ($1, $1); --
FOR rec IN EXECUTE ''SELECT * FROM tmp_all_user_flags'' LOOP
RETURN NEXT rec; --
END LOOP; --
DROP TABLE tmp_all_user_flags; --
RETURN null; --
END; --
' LANGUAGE plpgsql;
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
