Re: Should I use PL/PGSQL or Perl/PGSQL?

[Tyler MacDonald <tyler@xxxxxx>]

Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
> On 2/3/06, Tyler MacDonald <tyler@xxxxxx> 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;