Skip site navigation (1) Skip section navigation (2)

Re: noobie-ish question - selecting into a string

From: David Fetter <david(at)fetter(dot)org>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: noobie-ish question - selecting into a string
Date: 2005-04-25 23:12:05
Message-ID: 20050425231205.GG21513@fetter.org (view raw or flat)
Thread:
Lists: sfpug
On Mon, Apr 25, 2005 at 01:11:20PM -0700, Ben Riddell wrote:
> Howdy,
> 
> I'm wading my way through some postgresql that's just a little beyond me 
> right now. (PG 7.3.4, PHP 4.3.4, Fedora Core 1)
> 
> Could someone tell me how to do this, or show me a better way?
> 
> I'm creating some dynamic forms, from db tables like this:
> 
> table1: chemical - longish (700+) list of chemicals, makes for a chunky 
> drop-down
> chemical_id serial
> chemical_name varchar
> 
> table2: chemical_type - 4 rows, each chemical has from 1-4 types 
> associated, so table 3 maps
> chemical_type_id serial
> chemical_type varchar
> 
> table3: chemical_chemical_type_map
> chemical_id (fkeyed to chemical.chemical_id)
> chemical_type_id (fkeyed to chemical_type.chemical_type_id)
> 
> I need [x] dropdowns, determined by user input on the previous page. The 
> option values are the serial chemical_id from chemical, and the visible 
> text is chemical.chemical_name.
> 
> The same chemical may be listed more than once (with different 
> chemical_id's in the chemical table as well - once as type 1, once as type 
> 2, and maybe again as type 3 & 4 as well. I need to differentiate between 
> them in the form.
> 
> So what I need in this is a text string of the chemical_type_id values to 
> put into the dropdowns after the chemical names.
> 
> I guess I don't need to look at the chemical_type table, but it's there to 
> supply a full picture.
> 
> Is there a way to select the [1-4] values from chemical_type into the 
> result as a string? (Or is there an entirely different way to do this?)
> 
> $chemical_query = "
> SELECT c.*
> , [this would be where I'm stuck...] as chemical_type_list
> FROM chemical c
> , chemical_chemical_type_map cctm
> WHERE c.chemical_id = cctm.chemical_id
> ORDER BY c.chemical_id
> ";

I'd do something like this:

$chemical_query = <<<SQL
/* 'SELECT *' is not production code :) */
SELECT
    c.chemical_id,
    c.chemical_name,
    array_to_string(ARRAY(
        SELECT ct.chemical_type_id || '|' || ct.chemical_type
        FROM chemical_type ct
        JOIN chemical_chemical_type_map cctm
        ON (
            ct.chemical_type_id = cctm.chemical_type_id
        AND
            cctm.chemical_id = c.chemical_id
        )
    ), ',') AS "comma_separated_string_with_chemical_ids_and_types"
FROM chemical c
SQL;

If your version of PostgreSQL doesn't have array operators, it's time
to upgrade.

HTH :)

Cheers,
D
-- 
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

In response to

Responses

sfpug by date

Next:From: Josh BerkusDate: 2005-04-25 23:33:55
Subject: Re: noobie-ish question - selecting into a string
Previous:From: Josh BerkusDate: 2005-04-25 22:43:32
Subject: Re: noobie-ish question - selecting into a string

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group