Re: Permissions to create temporary tables
On 19/02/10 at 11:45 +0100, Andreas Tille wrote:
> 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;
>
> ***********************************************************************************/
Hi,
I don't really understand what this function is doing.
Remember that creating temporary tables causes disk writes in
postgresql. Is there really no other solution?
--
| Lucas Nussbaum
| lucas@lucas-nussbaum.net http://www.lucas-nussbaum.net/ |
| jabber: lucas@nussbaum.fr GPG: 1024D/023B3F4F |
Reply to: