Re: noobie-ish question - selecting into a string

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!