Permissions to create temporary tables

[Andreas Tille <andreas@an3as.eu>]

On Mon, Feb 08, 2010 at 11:09:43AM +0100, Andreas Tille wrote:
> I want to hear your opinion about
>    udd/sql/versions_archs_components.sql
> which creates a temporary table in a function which is not allowed to
> any user. Do we want to allow creating these tables or would you think
> I should try to find a different solution?

I just copy the code of the function to the end of this mail.
I would like to hear your opinion about giving permissions
to create temporary tables.

Kind regards

        Andreas.


/***********************************************************************************
 * Obtain available versions in different releases for a given package             *
 * This function takes a package name as argument and returns a table containing   *
 * the release names in which the package is available, the version of the package *
 * in this release and a string contained an alphabethically sorted list of        *
 * architectures featuring these version.  In the last column the component is     *
 * given.                                                                          *
 * See below for an usage example.                                                 *
 ***********************************************************************************/

CREATE OR REPLACE FUNCTION versions_archs_component (text) RETURNS SETOF RECORD AS $$
    Declare
        package     ALIAS FOR $1 ;

        r           RECORD;
        q           RECORD;
        query       text;
        query1      text;
    BEGIN
        -- make sure we have the components in reasonable order
        query = 'SELECT component FROM (SELECT component, version FROM packages WHERE package = '''
                 || package || ''' GROUP BY component, version ORDER BY version) AS cv GROUP BY component;';

        FOR r IN EXECUTE query LOOP
            query1      = /* -- DROP TABLE IF EXISTS tmpReleaseVersionArch ; */
                 'CREATE TEMPORARY TABLE tmpReleaseVersionArch AS
                 SELECT release || CASE WHEN char_length(substring(distribution from ''-.*'')) > 0
                                        THEN substring(distribution from ''-.*'')
                                        ELSE '''' END AS release,
                            -- make *-volatile a "pseudo-release"
                        regexp_replace(version, ''^[0-9]:'', '''') AS version,
                        architecture AS arch, component
                    FROM packages
                   WHERE package = ''' || package || ''' AND component = ''' || r.component || '''
                   GROUP BY architecture, version, release, distribution, component
                 ;' ;
            EXECUTE query1;
            query1 = 'SELECT release, version, array_to_string(array_sort(array_accum(arch)),'',''), CAST('''
                 || r.component || ''' AS text) AS component FROM tmpReleaseVersionArch
                 GROUP BY release, version ORDER BY version;' ;
            FOR q IN EXECUTE query1 LOOP
                RETURN NEXT q;
            END LOOP;
            DROP TABLE tmpReleaseVersionArch ;
        END LOOP;
    END; $$ LANGUAGE 'plpgsql';

/***********************************************************************************
 * Example of usage: Package seaview which has versions is in different components *

   SELECT r as release, version, archs, component
     FROM versions_archs_component('seaview') AS (r text, version text, archs text, component text)
          -- you have to specify the column names because plain RECORD type is returned
     JOIN releases ON releases.release = r
          -- JOIN with release table to enable reasonable sorting
    WHERE r NOT LIKE '%-%'
          -- ignore releases like *-security etc.
    ORDER BY releases.sort;

 ***********************************************************************************/


-- 
http://fam-tille.de