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
| lu...@lucas-nussbaum.net http://www.lucas-nussbaum.net/ |
| jabber: lu...@nussbaum.fr GPG: 1024D/023B3F4F |
--
To UNSUBSCRIBE, email to debian-qa-requ...@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org
Archive: http://lists.debian.org/20100220150556.gc13...@xanadu.blop.info
